Power Query: Filtering of Choice Part 3
11 September 2024
Welcome to our Power Query blog. This week, I continue finding a solution to a
challenge involving a choice of filters by transforming Tbl_Sales_Data.
In this series, I am considering a filtering challenge. Essentially, I have a Table of data, Tbl_Sales_Data, shown below::

I will be filtering this data, by comparing values from one of these Tables, which have been named Tbl_Dates, Tbl_Amounts and Tbl_Person:

The choice of which Table to use is controlled by a dropdown, which has been given the Named Range P_Choice and another translation Table, Tbl_Translation:

In Tbl_Translation, the first column, Choice will link to the value from the dropdown. The second column Translation, then translates to a column from the data Table. All of the data has been entered manually and the results will be driven by the value selected in P_Choice.

Last week, I started with the queries I had extracted to Power Query, and combined them to build on to the P_Choice query:

The final step was to change the data type of Value to ‘Text’. The filter choices are ready; this week I move onto the Tbl_Sales_Data query and apply the filters. I begin by removing the auto-generated ‘Changed Type’ step. It is important that all the columns I am comparing have the same data type; therefore, I set them all to ‘Text’:

This is to prevent any mismatch errors. I choose to create a ‘Custom Column’ from the ‘Add Columns’ tab:

I enter some M code:

I have named the column Match, and the M code is:
(CheckStrings) =>
List.AnyTrue(List.Transform(P_Choice[Value], each Text.Contains((Table.Column(CheckStrings,P_Choice[Translation]{0})),_ )))
This is entering a function CheckStrings() which is using list functionality to indicate if there is a match in the column identified by Translation of P_Choice for the values in Value of P_Choice. However, this returns an error:

Instead of using a record from the query P_Choice:
P_Choice[Translation]{0}
I need to extract this into a parameter containing the value. I begin by taking a reference copy of P_Choice:

I may then right-click on any cell in the Translation column and opt to ‘Drill Down’:

I call the new query / parameter P_Choice_Column:

Back in the query Tbl_Sales_Data, I amend the custom column I created:

The M code is now:
= (CheckStrings) =>
List.AnyTrue(List.Transform(P_Choice[Value], each Text.Contains((Table.Column(CheckStrings,P_Choice_Column)),_ )))
This time, the resulting column contains a Boolean:

I may filter to exclude the ‘FALSE’ values in Matched:

I have the data I need. It just remains for me to remove the Matched Column and use CTRL+ A whilst in any of the headings to allow me to use the ‘Detect Data Type’ option from the Transform Tab:

My data is ready to be loaded into the Excel workbook, where I will check that it works for all choices.
Come back next time for more ways to use Power Query!