This article describes the use of Expression.Evaluate as a very helpful “swiss-army-knife”-method for your Power BI toolbox as it has many more use cases than the one described below. It lets you perform repeating tasks without using functions and can even replace recursive operations in some cases – but that’s a topic for a later blogpost. Today we start with a simple task:
If you want to filter rows only where none of the fields/columns are empty, you’d either pass not-null filters into every single column (which is tedious and not dynamic), transpose or add an index column, unpivot and filter from there (both might drag performance down on large tables).
Using Expression.Evaluate will create a dynamic approach that doesn’t force you to transform your source table nor to directly specify your column names: It will take in a text string and execute it as if you’ve been written it manually into the formula. If you manage to let this textstring be created dynamically according to the different tables you’re going to pass in – Expression.Evaluate will make sure that these individual statements will be executed accordingly.
So for a table containing Column “A” and Column “B” the filter statement would look like this:
FilteredTable = Table.SelectRows(Source, each [A] <> null and [B] <> null)
And for a table with columns “You”, “should”, “know”, “that” like this:
FilteredTable = Table.SelectRows(Source, each [You] <> null and [should] <> null and [know] <> null and [that] <> null)
So what stays the same is the blue one and what needs to be dynamically created is the red/green one.
You’d call it like this:
FilteredTable = Table.SelectRows(Source, each Expression.Evaluate(Expression))
So how to automatically create the Expression.Evaluate-textstring then?
If we’d have a list of all the red items, we could simply combine them using “and ” as the combiner: Text.Combine(List, “and “)
As a starting point for the list we can take the table header (Table.ColumnNames) and transfer it to a table:
Then add a column that adds the other text-parts:
Table.AddColumn(Custom1, “Text”, each “[“&[Column1]&”] <> null”)
This will create a new column that contains the text parts for our string:
So the expression will then be created like this:
So far for the fun part. If you’re now executing it, you will be greeted with the following error-message:
This error-message is due to the missing definition of the environment as described in Chris Webb’s article linked to above . In this case, it’s pretty simple: [ _ = _ ]. Resulting in:
Table.SelectRows(Source, each Expression.Evaluate(Expression, [ _=_ ]))
As you can read in Chris’ latest blogpost this underscore creates a kind of row-context by passing on all values of the current row. Although it can be omitted in many cases, here we need it.
I was made aware of the tremendous power of Expression.Evaluate by Ivan Bond’s blogpost. But already recognized some potential downsides: You might stop looking for more elegant solutions that some unrecognized M-functions could provide because you can just Express…-code it. …Treasures have their price 🙂FilterNonEmptyColumns.xlsx
Enjoy & stay queryious 🙂