Filtering with parameters 189
Filtering with parameters
Parameters are a key functionality when it comes to the definition of dynamic filtering logic. You can create parameters that will be used to filter and load data according to predefined values. This way, you will be able to work on a subset of data optimizing general queries' performance.
In this recipe, you will see how to create a parameter over a key value, for example, a product key.
Getting ready
For this recipe, you need to download the FactInternetSales CSV file. In this example, we will refer to the C:\Data folder.
How to do it…
Once you open your Power BI Desktop application, you are ready to perform the following steps:
1. Click on Get data and select the Text/CSV connector.
Figure 6.11 – Text/CSV connector
190Optimizing Power Query Performance
2.Browse to your local folder where you downloaded the FactInternetSales. csv file and open it. The following window with a preview of the data will pop up. Click on Transform Data.
Figure 6.12 – CSV data preview
3. Browse to the Home tab and click on Manage Parameters.
Figure 6.13 – Manage Parameters button
Filtering with parameters 191
4.Create a parameter called ProductKey, select Decimal Number for Type from the relative drop-down section, select List of values from the Suggested Values dropdown, and enter the values 310, 346, and 336, as shown in the following screenshot:
Figure 6.14 – Manage Parameters window
192Optimizing Power Query Performance
5.Select the value 310 from the dropdowns for both Default Value and Current Value and click on OK.
Figure 6.15 – Defining default and current values
6.Browse to the Queries pane, select the ProductKey (310) parameter, and observe how you can select one of the three values you defined for that parameter, as shown in the following screenshot:
Figure 6.16 – ProductKey parameter
Filtering with parameters 193
7.From the Queries pane, select the FactInternetSales query. Then, select the ProductKey column from the query selected. Then, click on the drop-down icon on the right part of the ProductKey column and click on Number Filters and then Equals…, as shown in the following screenshot:
Figure 6.17 – Filtering the ProductKey column
194Optimizing Power Query Performance
8.Click on the type of value you want to base your equality on (the default one is 1.2) and select Parameter.
Figure 6.18 – Filter Rows window
9.ProductKey will be selected automatically since it is the only parameter in the current session. After this, click on OK.
Figure 6.19 – Filtering with parameters
Filtering with parameters 195
10.You can see how the ProductKey column is filtered on the current value 310 that we defined previously:
Figure 6.20 – Filtered column
11.Select the ProductKey (310) parameter on the Queries pane and change the value of the parameter from the drop-down section by selecting 346.
Figure 6.21 – Selecting a parameter value
196Optimizing Power Query Performance
12.Select the FactInternetSales query and observe how the filtered data changes.
Figure 6.22 – Filtered column
You can see how it is easy to dynamically apply filters with the use of parameters by defining a list of values.
In this example, we used a list of three ProductKey values that we created manually, but what if we want to retrieve this list from an external query?
You can do that by performing the following steps:
1.Go to the query settings on the right pane of the Power Query UI and delete the
Filtered Rows step.
Filtering with parameters 197
Figure 6.23 – Deleting the Filtered Rows step
2. Right-click on the ProductKey column and click on Add as New Query.
Figure 6.24 – Adding a column as a new query
198Optimizing Power Query Performance
3.A list will be generated with unique columns containing ProductKey values. Rename the query ProductKeyList.
Figure 6.25 – Renaming a list
4.Right-click on List and click on Remove Duplicates in order to have unique values only.
Figure 6.26 – Remove Duplicates
Filtering with parameters 199
5. Browse to the Home tab and click on Manage Parameters.
Figure 6.27 – Manage Parameters button
6.Edit the ProductKey parameter by selecting Query from the drop-down section of the Suggested Values field.
Figure 6.28 – Values from a query
200Optimizing Power Query Performance
7.Then, select ProductKeyList from the drop-down section of the Query field and click on OK to create the parameter.
Figure 6.29 – Selecting ProductKeyList for Query
8.From the Queries pane, select the FactInternetSales query. Then, select the ProductKey column from the same query. Then, click on the drop-down icon on the right part of the ProductKey column and click on Number Filters and then Equals…, as shown in the following screenshot:
Filtering with parameters 201
Figure 6.30 – Filtering a column
9. Click on the type of value you want to base your equality on and select Parameter.
Figure 6.31 – Filter Rows window
202Optimizing Power Query Performance
10.ProductKey will be selected automatically since it's the only parameter in the current session. After this, click on OK.
Figure 6.32 – Using a parameter as a filter
11.Browse to the Home tab, click on the Manage Parameters dropdown, and then click on Edit Parameters.
Figure 6.33 – Edit Parameters button