Using functions to create a filter in Power Query

Published by Thomas foster on

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. To define this by a function we need to use Expression.Evaluate. This function takes a string and evaluates it as if it were power query m code. However, if you just insert the function right in you will get an expression error 

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

This has 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


Leave a Reply

Your email address will not be published. Required fields are marked *