Power Query column name as parameter

Question

dear wizards)

I'm trying to create a search function where I could use input: 1. Table for search 2. Column of this Table in which search will run 3. Value to search in the column specified in 2

the function looks like this:

( mTbl as table, mColName as text, mColValue as text) =>

let
    Source = mTbl,
    FilteredTable = Table.SelectRows(Source, each ([ mColName ] =  mColValue )),
    Result = List.Count(FilteredTable[ mColName ])
in
    Result

yet it results in Error:

Expression.Error: The column 'mColName' of the table wasn't found. Details: mColName

Could there be any suggestions? Many thanks in advance

Aldert · Accepted Answer · 2018-08-04 07:55:41Z

13

I had the same need but Table.Columns(Source,mColName) was not working because this is returning a list. In my problem I needed a filtering of the column returned as table.

I solved it as following:

( mTbl as table, mColName as text, mColValue as text) =>

    let
    Source = mTbl,
    Result = Table.SelectRows(Source, each (Record.Field(_, mColName) =  mColValue ))
in
    Result

answered Aug 4, 2018 at 7:55

Aldert

4,3631 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MarcelBeug · Accepted Answer · 2017-06-01 05:18:04Z

5

Field references like [mColName] are never dynamic, so the code will try to use a field with the name "mColName"; it won't be substituted by the string in parameter mColName.

Instead, you can use: Table.Column(Source,mColName)

answered Jun 1, 2017 at 5:18

MarcelBeug

3,0171 gold badge12 silver badges11 bronze badges

Comments

lkjldfkjhljk · Accepted Answer · 2020-03-12 11:28:28Z

3

One more possible solution is to rename your column to some name that is known inside the function. So, there will be no need in dynamic column name in SelectRows function:

( mTbl as table, mColName as text, mColValue as text) =>
let
    Source = mTbl,
    SortColumnName = "XXXXX",
    Renamed = Table.RenameColumns(Source, {{mColName , SortColumnName}}),
    FilteredTable = Table.SelectRows(Renamed, each ([XXXXX] =  mColValue)),
    Result = List.Count(FilteredTable[SortColumnName])
in
    Result

answered Mar 12, 2020 at 11:28

lkjldfkjhljk

667 bronze badges

1 Comment

Denis Over a year ago

Thank you, @SashaM, so much! Only your solution works for my problem: RANK function in/for Power Query, others don't (at least I don't know).

Collectives™ on Stack Overflow

Power Query column name as parameter

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related