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

score 13 · Answer 1 · answered Aug 04 '18 at 07:55

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

score 5 · Answer 2 · answered Jun 01 '17 at 05:18

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 01 '17 at 05:18

MarcelBeug

2,872
1
8
10

score 3 · Answer 3 · answered Mar 12 '20 at 11:28

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

Thank you, @SashaM, so much! Only your solution works for my [problem: RANK function in/for Power Query](https://stackoverflow.com/a/72830541/6165594), others don't (at least I don't know). — Denis, Jul 01 '22 at 14:23

Power Query column name as parameter

3 Answers3

Linked