Power Query: Filtering of Choice Part 1
28 August 2024
Welcome to our Power Query blog. Today, I consider a challenge involving a choice of filters, and I begin by extracting and transforming the input data.
This week, I have a new challenge. 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)
For example, if P_Choice is ‘Salesperson’, then Tbl_Sales_Data will be filtered to only include rows where the Sales Manager matches a value inTbl_Person. If P_Choice is ‘Date’ or ‘Amount’, then the rows must match values in Tbl_Dates or Tbl_Amounts respectively.
I will begin by extracting Tbl_Sales_Data into Power Query. I choose to do this by right-clicking anywhere in the table, and choosing to ‘Get Data from Table/Range…’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1723650566.png/49ce22de6deb2fcb6c13b669ef7534e0.jpg)
This brings the data into the Power Query editor:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1723650585.png/8b42fde3ba33a0dbae73beb8da52f965.jpg)
I ‘Close & Load to…’ from the Home tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1723650610.png/351e5e19cc913b2623cd80435f61aac4.jpg)
In the Excel workbook, I choose to ‘Only Create Connection’. I have no need to load the data to the workbook yet:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1723650635.png/c5287ac63b7b11bda2ea0b6cecec4949.jpg)
I repeat this process for the other Tables in the workbook, until I have the Named Range P_Choice left to extract using the same method:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1723650657.png/a1c3790208e03f346d51a77b511016c4.jpg)
When I extract the data to Power Query, the default action is to ‘Promote Headers’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1723650688.png/8f85d6bc8dbab7822656a654f011ae62.jpg)
I select the ‘Promoted Headers’ step and right-click to ‘Delete Until End’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1723650711.png/52dce114a0d0ec436d5cb2c5a7126b97.jpg)
This leaves me with the Source step. I want to keep the value as a table as I wish to merge with other data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1723650745.png/1285ce68f582716f78c6de4735f8db16.jpg)
I have all the data from ‘Sheet1’ extracted and ready to solve the challenge. Next time, I will transform P_Choice by combining more data.
Come back next time for more ways to use Power Query!