- •Contributors
- •Table of Contents
- •Preface
- •Technical requirements
- •Installing a Power BI gateway
- •Getting ready
- •How it works
- •Authentication to data sources
- •Getting ready
- •How it works
- •Main challenges that Power Query solves
- •Getting ready
- •Technical requirements
- •Getting data and connector navigation
- •Getting ready
- •Creating a query from files
- •Getting ready
- •How it works...
- •Creating a query from a folder
- •Getting ready
- •How it works...
- •Creating a query from a database
- •Getting ready
- •How it works...
- •Creating a query from a website
- •Getting ready
- •How it works...
- •Technical requirements
- •Exploring Power Query Editor
- •Getting ready
- •Managing columns
- •Getting ready
- •Using data profiling tools
- •Getting ready
- •Using Queries pane shortcuts
- •Getting ready
- •Using Query Settings pane shortcuts
- •Getting ready
- •Using Schema view and Diagram view
- •Getting ready
- •Technical requirements
- •Formatting data types
- •Getting ready
- •Using first rows as headers
- •Getting ready
- •Grouping data
- •Getting ready
- •Unpivoting and pivoting columns
- •Getting ready
- •Filling empty rows
- •Getting ready
- •Splitting columns
- •Getting ready
- •Extracting data
- •Getting ready
- •Parsing JSON or XML
- •Getting ready
- •Exploring artificial intelligence insights
- •Getting ready
- •Technical requirements
- •Merging queries
- •Getting ready
- •Joining methods
- •Getting ready
- •Appending queries
- •Getting ready
- •Combining multiple files
- •Getting ready
- •Using the Query Dependencies view
- •Getting ready
- •Technical requirements
- •Setting up parameters
- •Getting ready
- •Filtering with parameters
- •Getting ready
- •Folding queries
- •Getting ready
- •Leveraging incremental refresh and folding
- •Getting ready
- •Disabling query load
- •Getting ready
- •Technical requirements
- •Using M syntax and the Advanced Editor
- •Getting ready
- •Using M and DAX – differences
- •Getting ready
- •Using M on existing queries
- •Getting ready
- •Writing queries with M
- •Getting ready
- •Creating tables in M
- •Getting ready
- •Leveraging M – tips and tricks
- •Getting ready
- •Technical requirements
- •Adding columns from examples
- •Getting ready
- •Adding conditional columns
- •Getting ready
- •Adding custom columns
- •Getting ready
- •Invoking custom functions
- •Getting ready
- •Clustering values
- •Getting ready
- •Technical requirements
- •Using Power BI dataflows
- •Getting ready
- •Centralizing ETL with dataflows
- •Getting ready
- •Building dataflows with Power BI Premium capabilities
- •Getting ready
- •Understanding dataflow best practices
- •Getting ready
- •Technical requirements
- •Exploring diagnostics options
- •Getting ready
- •Managing a diagnostics session
- •Getting ready
- •Designing a report with diagnostics results
- •Getting ready
- •There's more…
- •Using Diagnose as a Power Query step
- •Getting ready
- •Other Books You May Enjoy
- •Index
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