CODEWITHSUNDEEP
functionpowerquery
Sergiy Razumov
Sergiy Razumov

Reputation: 159

Power Query column name as parameter

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

Upvotes: 8

Views: 10404

Answers (3)

lkjldfkjhljk
lkjldfkjhljk

Reputation: 66

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

Upvotes: 3

Aldert
Aldert

Reputation: 4323

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

Upvotes: 13

MarcelBeug
MarcelBeug

Reputation: 2997

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)

Upvotes: 5

Related Questions