- •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
212Optimizing Power Query Performance
3.Go to APPLIED STEPS and right-click on the Navigation step. You can see that you can't select View Native Query and this means that you can't leverage query folding, as shown in the following screenshot:
Figure 6.45 – View Native Query disabled
When you perform some changes on the data, when you add columns and enrich the content of your queries, you will probably lose the query folding feature. The best practice is to perform the steps when query folding is active at the beginning in order
to send a single statement to the data source, and this will end up improving the overall performance, both in Import and Direct Query mode.
Leveraging incremental refresh and folding
When you load data from Power Query, you do not perform a one-time load, but usually, you need to refresh data in order to load new data or to update existing data. When loading data incrementally, it is possible to leverage parameters and query folding in order to optimize and retrieve data quickly. In this recipe, we will see how to set up time parameters and incremental refresh.
Leveraging incremental refresh and folding 213
Getting ready
In this recipe, you need to connect to an Azure SQL database that you can recreate in your environment with the Adventureworks.bacpac file.
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 click on More… to access the Get Data window and to see the complete list of connectors.
Figure 6.46 – Power BI connectors
214Optimizing Power Query Performance
2.Browse to the Azure SQL database connector, select it, and click on Connect.
Figure 6.47 – Azure SQL database connector
Leveraging incremental refresh and folding 215
3.Enter your server and database information, flag Import for Data Connectivity mode, and then click on OK.
Figure 6.48 – SQL Server database information
4.Authenticate with your preferred authentication method. In this example, I'm using the Microsoft account authentication:
Figure 6.49 – SQL Server database authentication
216Optimizing Power Query Performance
5.Select the FactInternetSales table from the database and click on
Transform Data.
Figure 6.50 – Selecting tables from a database
Leveraging incremental refresh and folding 217
6. Browse to the Home tab and click on the Manage Parameters button.
Figure 6.51 – Manage Parameters button
7.Create a RangeStart parameter with Date/Time for Type and Any value for
Suggested Values and enter 29/12/2010 00:00:00 for Current Value, as shown in the following screenshot:
Figure 6.52 – Creating the RangeStart parameter
218Optimizing Power Query Performance
8.Create a second parameter, RangeEnd, with the same settings as the previous one, and then enter 05/01/2011 00:00:00 for Current Value and click on OK.
Figure 6.53 – Creating the RangeEnd parameter
Leveraging incremental refresh and folding 219
9.Select the FactInternetSales query and select the OrderDate column. Click on the filter icon, then Date/Time Filters, and then Custom Filter….
Figure 6.54 – Applying Custom Filter...
220Optimizing Power Query Performance
10.The Filter Rows window will pop up. Enter the two parameters as filter options, as shown in the following screenshot, and click on OK to apply the custom filters:
Figure 6.55 – Filter Rows window
In this way, we are defining a subset of data between a range defined by the parameters. The values of those parameters are not important at this time, because this acts just as a sample subset of data that will be managed by the incremental refresh rule that will be set later.
11. Click on Close & Apply to load all the queries within the model.
Figure 6.56 – Close & Apply
Leveraging incremental refresh and folding 221
12.Go to the Fields section, right-click on the FactInternetSales table, and click on Incremental refresh.
Figure 6.57 – Setting up incremental refresh
222Optimizing Power Query Performance
13.After having loaded the model with a sample subset of data defined by the two parameters, you can now set up a rule that will be used when you refresh the data from the Power BI service. You can select the table on which to apply the incremental refresh, enable the feature, and define which rows to store based on a historical period. In this case, we will store all data from the last 15 years. Then, you can define which time range to consider when refreshing data. In this case, we defined
a 2-month refresh range, which means that RangeStart and RangeEnd, the two date parameters defined previously, will adapt to this rule and that subset of data will be updated and added. Once you set this up, leave unflagged the optional settings (Detect data changes, which allows you to update data that has changed, and Only refresh complete months), as shown in the following screenshot, and click on Apply all:
Figure 6.58 – Incremental refresh details