Power Query: Filtering of Choice Part 2

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!