Using functions to create a filter in Power Query

The function Table.SelectRows is a very common power query function that is used to filter a table. It is what is created when you filter rows in a table using the UI. It takes a table and then applies whatever filters are set in the second argument. The following code generates a basic filtered table.

let
    Table = Table.FromRecords(
        {  
            [Id = 1,  Name = "Amanada", Age = 45],
            [Id = 2, Name = "Bob", Age = 13],
            [Id = 3, Name = "Christina", Age = 37],
            [Id = 4, Name = "David", Age = 23]
        }
    ),
    Changed_Type = Table.TransformColumnTypes(Table,{{"Id", Int64.Type}, {"Age", Int64.Type}, {"Name", type text}}),
    Filtered_Rows = Table.SelectRows(Changed_Type, each [Age] > 20)
in
    Filtered_Rows

The key element of this is the “[Age] > 20” this is the part of the query that specifies the conditions. If you try to define a string elsewhere and then use that string to filter you will get an error.

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

To fix this we need to use Expression.Evaluate. This function takes a string and evaluates it as if it were power query m code.

This happens occurred because the it is trying to evaluate the filter expression in its own environment where the fields of the table are not present. To fix this we need to evaluate the expression in the previous environment, this can be done by using the second argument of Expression.Evaluate. 

let
    Table = Table.FromRecords(
        {  
            [Id = 1,  Name = "Amanada", Age = 45],
            [Id = 2, Name = "Bob", Age = 13],
            [Id = 3, Name = "Christina", Age = 37],
            [Id = 4, Name = "David", Age = 23]
        }
    ),
    Changed_Type = Table.TransformColumnTypes(Table,{{"Id", Int64.Type}, {"Age", Int64.Type}, {"Name", type text}}),
    Filter = "[Age] > 20",
    Filtered_Rows = Table.SelectRows(Changed_Type, each Expression.Evaluate(Filter, [_=_]))
in
    Filtered_Rows

This will now evaluate as expected giving the same result as the first query. There is so much more that can be done using the evaluation enviroment. If you want to read more about Expression.Evaluate I would recommend these two blog posts, one by Chris Webb and one by the BI Accountant

• Power-Bi
• Power-Query