Disabling query load 223
Once you publish the model on the Power BI service, you will be able to trigger the refresh from there and update data quickly. Query folding is key to achieve this functionality because when you send the refresh query to the source, you will be applying a filter that will be included in the statement sent to the source.
Disabling query load
Queries' loads can be heavy and sometimes refreshing some tables can impact negatively on performance. This is why it is important to know what data is concretely needed
for end users. It is common that you will need some queries just for transformations in Power Query, but you won't need them in the final model. In this recipe, we will see
how you can use some queries for enriching data needed for reporting and how you can disable the loading of this supporting table in order to reduce the impact on performance and refreshing.
Getting ready
For this recipe, you need to download the following files:
•The FactInternetSales CSV file
•The DimTerritory CSV file
In this example, we will refer to the C:\Data folder.
224 Optimizing Power Query Performance
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.59 – Text/CSV connector
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.
Disabling query load 225
Figure 6.60 – CSV data preview
3.Repeat the previous two steps and load the DimTerritory.csv file.
4.Select DimTerritory in the Queries pane, browse to the Home tab, and click on the
Merge Queries button.
Figure 6.61 – Merge Queries button
226Optimizing Power Query Performance
5.The Merge window will pop up. Select FactInternetSales as the table to merge with DimTerritory and select the SalesTerritoryKey column from both tables. Select Left Outer (all from first, matching from second) and click on OK.
Figure 6.62 – Merge window
Disabling query load 227
6.Click on the expand button on the right side of the FactInternetSales column. Flag Aggregate, select Sum of TotalProductCost and Sum of SalesAmount, and remove the flag from Use original column name as prefix, as shown in the following screenshot:
Figure 6.63 – Expanding columns
228Optimizing Power Query Performance
7.You should see the two newly added columns in the DimTerritory query.
Figure 6.64 – Newly added columns
8.Rename the DimTerritory query to Sales geography since it will be the table that we will load in the data model.
Figure 6.65 – Renaming queries
9.Right-click on FactInternetSales in the Queries pane and observe how, if you load the queries with the current setting, you will load both of the queries because they have Enable load flagged.
Disabling query load 229
Figure 6.66 – Enable load button
10.If you click on Enable load, you will remove the flag and you will see that the name of the query will turn into italics, which means that if you load the queries with these settings, you will only be loading the Sales geography query and not FactInternetSales, which in this case was used only to enrich the other query.
Figure 6.67 – Enable load button disabled
230 Optimizing Power Query Performance
While using Power Query and performing data transformation steps, many queries will be used to enrich others and there is no concrete need to load them all. This will leave you with poor performance and high loading and refresh times and will increase the complexity of the data model.
By disabling the loading of some queries, you won't lose the transformations applied, such as the merge in this recipe, but you won't need to load a high-volume table.
7
Leveraging the M Language
Power Query is based on M language, which stands for Power Query Formula Language. Every time you perform a Power Query step, you are essentially writing
M code. You can leverage the UI to transform your data without learning any M language at all but gaining an understanding of it could help you to customize even more Power Query transformations and perform quick corrections that are not possible with
the UI only.
In this chapter, we will give an outline of M coding, explaining its differences from Data Analysis Expression (DAX) language (a familiar language to Power BI users), and you will see how to use M code on existing queries and how to create queries from scratch.
You will explore M coding examples through the following recipes:
•Using M syntax and the Advanced Editor
•Using M and DAX – differences
•Using M on existing queries
•Writing queries with M
•Creating tables in M
•Leveraging M – tips and tricks