Today we are going to discuss about filtering the data in Power Query using a parameter.
This is the simplest form of parameterized query. If we can get used to that, we can move onto custom function in power query. Lot to learn, let's dive in.
This is our final output. Filter criteria is in the table format in the range E3:E4 and named as "Parameter". Filtered data is loaded in the range G3.
We have our source data as shown below and it has already been converted into table and named as "tData". We can keep our source table and final table in same sheet or in different sheets based on your preference.
If we change the region in Filter criteria and do refresh, then the final table will get updated based on the applied region filter.
Let me start this from scratch,
Click inside the source table and press shortcut key Alt + A + P + T to get the source data into power query editor.
List of queries in left pane, Steps of each query in right pane. Center part is for table and on top of table, you can see formula bar.
Remember that; Power query is case sensitive; Base is zero.
To get 'Parameter' table into power query editor:
In left side Queries pane, right click on the query 'tData' and select Duplicate option.
Query 'tData" is duplicated and named as 'tData(2)'. Right click on the new query and rename it as 'Criteria'.
In Applied Steps pane, delete Changed Type step.
In the formula bar, delete
{[Name="tData"]}[Content] and only keep
= Excel.CurrentWorkbook()
This formula picks all tables from the workbook.
At this level, screen will look like below,
To pick 'Parameter' table, we are going to do 2-way lookup in power query.
For that, we must know about,
Row index operator - {} (Curly braces)
Field access operator - [] (Square brackets)
We can do this in 2 methods,
Method -1:
Here, the 'Parameter' table is in the 2nd row, as I said earlier, power query is base zero and so this is row 1. So, we can simply hard code the row index operator as shown below.
Now, this table is converted into record with 2 columns [Content], [Name].
Table is there in the [Content] Column, to extract that,
Use Field access operator as shown below,
Method -2:
Instead of hard coding the row index operator, we can do Key match lookup to make it more dynamic as shown below.
Here, we get the row index operator based on the name 'Parameter'. Even if the row numbers of the table changed, this will pick the exact table.
If we add new table in future, 'Parameter' table can go into 3rd row, in that case hard coded query will pick the wrong table.
In both the method, query 'Criteria' is in the table format. We will come back to this later.
Now in 'tData' query, in the [Region] column simply apply filter and select 'East' Region as shown below,
In applied steps pane, one step got added namely 'Filtered Rows',
and in the formula bar, we can see power query automatically generated M-code for us.
Table is filtered for the region 'East'.
Arguments of Table.SelectRows formula are,
In table argument, it picked earlier step as the input #"Chnaged Type".
In condition argument, each is the key word for custom function. it means iterate in each row of the table. Then condition provided is [Region] = "East".
We have to replace text "East" to make it dynamic. We have that replacement part but in the table format. To convert table into text we can simply do drill down, or we can even do 2-way lookup. Let me show you how to do that.
In Criteria table, right click on the row and select drill down.
You can see on the formula bar, it did 2-way lookup for us.
In the left pane, you can see that icon for 'Criteria" is changed into ABC.
Power query tells us that it is a text.
Now, we can use "Criteria" as our parameter to make the filter process dynamic as shown below.
Now, we replaced the text part with our parameter "Criteria" and the table is already filtered for the region 'South'.
To load this table into excel sheet,
In menu bar -> Home tab -> Click Drop down in Close & Load -> Close and Load to
Now, 'Import data' dialog box will open, select table option and Existing sheet, click on the cell based on your preference.
In Parameter table in excel, change the region into 'West', and press shortcut key Ctrl + Alt + F5 to refresh. Our final table filtered for West region, loaded as shown below.
Kommentare