Power Query: Filtering of Choice Part 4
18 September 2024
Welcome to our Power Query blog. This week, I complete the solution to the challenge involving a choice of filters.
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 completed the transformations:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1723658090.png/8cc4d2c35b965bb6dd402553029f32ad.jpg)
I am ready to load and test the solution. I choose to ‘Close & Load To..’ in order to control which queries are loaded to the Excel Workbook:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1723658113.png/80dbbe8a1fba8143081eb12b2e3190a0.jpg)
I choose to ‘Only Create Connection’ to begin with, and then I may right-click on the Tbl_Sales_Data query to access ‘Load To…’ again:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1723658144.png/a22932c05f3e0607813c3257f774a779.jpg)
This time, I choose to load the data to a new worksheet:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1723658162.png/c01eab542b8703e562931409674bf12b.jpg)
My results are shown on a new sheet:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1723658182.png/d60109f035b569c404194536dca2ee34.jpg)
To test my query, I go back to ‘Sheet1’ and change the inputs:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1723658201.png/c89b8820ebce6a22a5474979fab49ef2.jpg)
Here, I choose to filter by the Date. I refresh the Tbl_Sales_Data query to see the results:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1723658220.png/7e771d269335dc6db6f57a61039e52f2.jpg)
The correct dates have been selected. Finally, I select to filter on ‘Amount’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1723658239.png/01d8d73e184a3213b7932c1513a266f5.jpg)
When I refresh, the correct data appears:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1723658259.png/6da938ef24564ab39d1a5a0f9707aee3.jpg)
Come back next time for more ways to use Power Query!