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

Answer 1

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

Answer 2

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)

Answer 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