A few days ago, I ran into a problem where I had to filter a product table by a dynamically set of columns – so in one solution it could be filtering by field x and y and in other solutions it had to be by field a, b and c.
In this case only rows that where not blank in either of these columns should be included in the result.
Hmmm… how can we do this using Power Query –
Here is the demo data
And if we only want the rows where Field A and Field B has the value “xx” we could add a filter statement (this can only by done if you modify the SelectRows formula manually! )
= Table.SelectRows(#”Changed Type”, each ([Field A] <> “” or [Field B] <> “”))
But what if we wanted to make the fields dynamically
Let’s start by creating a list containing the field names we want to filter
This list could also be retrieved by extracting data from a config file or a database if you would make it even more flexible – just remember it must be at list
Now you should know that any row in a table is a Record
And each record has fields and Power Query has functions related to Records.
One of this is the Record.SelectFields ( https://docs.microsoft.com/en-us/powerquery-m/record-selectfields ) and this function can be used to select specific fields in a record
So lets add a custom column with this formula
= Table.AddColumn(#”Filtered Rows”, “FilterMultipleColumns”, each Record.SelectFields(_, FieldList))
The first argument uses the _ statement and thereby referring to the current record – and the second is parameter containing the list of fields.
And as you can see when selecting a cell the record in the column now contains only of the fields we have specified in our list.
Now we turn this in to a list by using the function Record.ToList in our AddColumn statement
= Table.AddColumn(#”Filtered Rows”, “FilterMultipleColumns”, each Record.ToList(Record.SelectFields(_, FieldList)))
Notice that we now have a list in the column instead of a record
Next step is to use the function List.MatchesAny to check whether the contains our filter
= Table.AddColumn(#”Changed Type”, “FilterMultipleColumns”, each List.MatchesAny(Record.ToList(Record.SelectFields(_, FieldList)), each _ <> “”))
The second argument is then our filter for all fields and it will return TRUE or FALSE if the criteria is met.
And finally, we can filter the rows using the added Column and only include rows that return true
And if we want to filter by other columns/fields, we change the list
And it works
Hope you find this useful and let me know if you do
Here is a link to a demo pbix .
Query On !