How to replace a column name in Power Query by a variable to filter rows?

Question

In a query developed with Excel Power Query I would like to replace the table column name with a variable, when I filter rows of a list. Unfortunately, I either get an error message (e.g. Expression Error: Column xx of table was not found) or no row is remaining after filtering even if it should. In the example below the variable #"Filter" (= column name) is set to column "B" and used in the step #"Filter Rows" to filter the rows which contain a '1'. But the result is that no row is shown. Any idea what's the reason and how to solve this? Maybe the variable needs to have a special data type?

Thanks in advance!

Table: Table to be filtered

Code:

let
  #"Filter" = "B",
  Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{#"Filter", type text}}),
  #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Text.Contains(#"Filter", "1"))
in
  #"Filtered Rows"

Alejandro Lopez-Lago - MSFT Alejandro Lopez-Lago - MSFT · Accepted Answer · 2017-05-17T23:09:37

#"Filter" is the value "B", so the condition always returns false. The second parameter of Table.SelectRows takes a function which passes in the current row of the table as a record. You can do either of the following to use a variable to specify the column:

= Table.SelectRows(#"Changed Type", each Text.Contains(Record.Field(_, #"Filter"), "1"))

= Table.SelectRows(#"Changed Type", (x) => Text.Contains(Record.Field(x, #"Filter"), "1"))

each and _ are the same as (x) => and x in these cases. Record.Field(_, #"Filter") gets the value in the current row for the column #"Filter" refers to.

How to replace a column name in Power Query by a variable to filter rows?

1 Answers