Power Query: Filtering of Choice Part 2
4 September 2024
Welcome to our Power Query blog. This week, I continue to work on a challenge involving a choice of filters by transforming P_Choice.
Last week, I described my current 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.

Having extracted all the Tables and P_Choice to Power Query, I need to consider how I should begin to transform my data.
The key to solving this problem lies with lists. I need to convert the queries Tbl_Dates, Tbl_Amounts and Tbl_Person into a format where I have a list of values within the table and an identifier for the list, which will allow me to merge with P_Choice.
I begin in Tbl_Dates, where I remove the ‘Changed Type’ step, and choose to ‘Unpivot Columns’ from the right-click menu:

This gives me two [2] columns, the list of values and the identifier, as required:

I repeat this process for Tbl_Amounts:

I repeat the process one more time for Tbl_Person:

I am ready to start building from P_Choice. Starting from here, I need to merge with all three [3] of the unpivoted queries: Tbl_Dates, Tbl_Amounts and Tbl_Person, as I may need to get the data to filter by from any of these tables, depending upon the value in P_Choice. Unlike appending, I may only merge with one other table at a time. However, the data in the three unpivoted tables is in the same structure, therefore I may append them, and then merge the result with P_Choice.
I begin in Tbl_Dates, and choose to ‘Append Queries’ from the Home tab:

In the dialog, I choose to append ‘Three or more tables’, and whilst holding down the CTRL key, I select Tbl_Amounts and Tbl_Person from ‘Available tables’. I may then use the ‘Add’ button to create a list of ‘Tables to append’:

I click ‘OK’ to perform the append:

I have renamed the step to reflect the tables appended. I return to P_Choice, where I select ‘Merge Queries’ from the Home tab:

I choose to merge with Tbl_Dates:

I link Column1 from P_Choice to Attribute of Tbl_Dates and use the default ‘Left Outer’ join. The message that ‘The selection matches 1 of 1 rows from the first table’ indicates that at least one filter value has been selected:

I may now expand the data to find the filter values:

I only need the Value column, and I have no need to ‘Use original column name as prefix’:

The data is still not quite ready. I need to merge with Tbl_Translation, as I will need to know the column name on Tbl_Sales_Data.

I link Column1 of P_Choice to Choice of Tbl_Translation and use the default ‘Left Outer’ join again.

I rename the step to make it clear that I have picked up the translations. I may now expand the data:

Having selected the Translation column, I may now remove Column1 too:

Finally, I change the data type of Value to ‘Text’. The filter choices are ready; next time I will turn my attention to the Tbl_Sales_Data query.
Come back next time for more ways to use Power Query!