Using Diagnose as a Power Query step 379
In general, you can see which values are contributing to the increase in duration of a query and how different factors can influence the evaluation of a certain query.
The most frequent analysis is related to what happens when the refresh from Power Query is started and to see what the impact of different steps could be. In this case, we can see how the overall duration is higher in FactInternetSales-example1, where we alternate removing columns and data type changes, whereas in the second, FactInternetSales-example2, we consolidate the steps, and it turns out to be, as we would expect, more performant.
You can leverage Query Diagnostics to make these comparisons and get the most out of this analysis.
There's more…
In general, as you had the chance to see with this recipe, it is better to consolidate the same steps, such as removing columns, filtering, and changing data types, and not alternate them like in the FactInternetSales-example1 query.
Moreover, another key element that can be analyzed is query folding. As we saw in
Chapter 6, Optimizing Power Query Performance, in the Folding queries recipe, you can send a query directly toward your data source and with Power Query Diagnostics, you can see what has been pushed back and review it. It is important to perform all transformations that support folding at the beginning to optimize performance.
Using Diagnose as a Power Query step
In the previous recipes, you had the chance to see how to run a query diagnostic at the query level, but you also have the chance to investigate single Power Query steps, without running general diagnostics for the entire query, and then drill down to the step you are interested in. In this recipe, we will see how to use this feature and test a single step.
Getting ready
For this recipe, you need to download the FactInternetSales CSV file. In this example, we will refer to the C:\Data folder.
380 Implementing Query Diagnostics
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.
2.Browse to your local folder where you downloaded the FactInternetSales CSV file and open it. A window with a preview of the data will pop up; click on
Transform Data.
3.Select the ProductKey column, click on the drop-down icon, and click on
Number Filters and then Greater Than….
Figure 10.37 – Filtering on ProductKey
4.The Filter Rows window will appear. Enter the value 380 to keep rows where ProductKey is greater than that value and click on OK.
Using Diagnose as a Power Query step 381
Figure 10.38 – Filter Rows window
5.We will now diagnose the step we performed in the previous step. Navigate to APPLIED STEPS on the right side of the Power Query UI, right-click on the Filtered Rows step, and click on Diagnose, as in the following screenshot:
Figure 10.39 – Diagnose step from APPLIED STEPS
382 Implementing Query Diagnostics
You can also browse to the Tools tab and click on the Diagnose Step button to achieve the same result.
Figure 10.40 – Diagnose Step from the Tools tab
6.Under the Queries pane, you can see the output of Diagnose Step, similar to the ones seen in the previous recipes, but now, we focus only on that single step.
Figure 10.41 – Diagnose Step output
The diagnostic schema is the same as the one observed when we ran diagnostic queries in the previous sections of this chapter.
In general, Power Query offers you the possibility to evaluate the impact of different steps while developing your transformations and when refreshing queries in Power BI Desktop.
Packt.com
Subscribe to our online digital library for full access to over 7,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
•Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
•Improve your learning with Skill Plans built especially for you
•Get a free eBook or video every month
•Fully searchable for easy access to vital information
•Copy and paste, print, and bookmark content
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at packt.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at customercare@packtpub.com for more details.
At www.packt.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.