Power Query: Filtering of Choice Part 1

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:

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. 

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…’:

This brings the data into the Power Query editor:

I ‘Close & Load to…’ from the Home tab:    

In the Excel workbook, I choose to ‘Only Create Connection’.  I have no need to load the data to the workbook yet:

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:

When I extract the data to Power Query, the default action is to ‘Promote Headers’:

I select the ‘Promoted Headers’ step and right-click to ‘Delete Until End’:

This leaves me with the Source step.  I want to keep the value as a table as I wish to merge with other data.

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!