Power Query: Filtering of Choice Part 3

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:

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:

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:

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!