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::
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1723650480.png/f25d8c569a7f40f0793bdc5558e237d6.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1723650505.png/5c0ed772ca3fa04de81570fbbf1dcc0d.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1723650529.png/fd4e845a82887f077ed499bb624c2108.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1723650547.png/982b16cd93370699fffb609e53364c48.jpg)
Last week, I started with the queries I had extracted to Power Query, and combined them to build on to the P_Choice query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1723656688.png/a277ec0dccaaa951618c52c9d546af38.jpg)
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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1723656708.png/a38377e796cb3b7119e6f7a395092197.jpg)
This is to prevent any mismatch errors. I choose to create a ‘Custom Column’ from the ‘Add Columns’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1723656725.png/3cda7c91f7cf1a9bfe9445a3d3bc0e55.jpg)
I enter some M code:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1723656747.png/6c048b57e56b03cedd81bd69b3c22954.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1723656801.png/d928149ddedf3e6f51f2040345858447.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1723656846.png/43ffe664e38695e747372f9a26cc539c.jpg)
I may then right-click on any cell in the Translation column and opt to ‘Drill Down’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1723656864.png/b9505cde2c3411bbecc15f5b4c435fed.jpg)
I call the new query / parameter P_Choice_Column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1723656884.png/0d045671354715958f81e3e9dd3ecfc1.jpg)
Back in the query Tbl_Sales_Data, I amend the custom column I created:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1723656900.png/a2258ca9febbba20917508a6ebd90d3c.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1723656935.png/08d674ae7d8668248945888634dfe0f3.jpg)
I may filter to exclude the ‘FALSE’ values in Matched:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1723656957.png/46765d12cc2d0d3298fae7f299bacfb1.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1723656978.png/f64550b1befd54e70c648cc2400e7cc2.jpg)
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!