Identifying Performance Issues

In general, organizations are required to address performance issues at the time of running reports. Power BI offers the Performance Analyzer tool to fix problems and streamline the process.

Steps to Optimize performance in Power Query

At the data source level, the performance in Power Query is based on the performance and handling performance issues. The type of data sources that Power Query offers is very extensive, and the performance tuning techniques for each source are equally wide. In order to optimize the SQL Server performance tuning techniques requires index creation, hardware upgrades, execution plan tuning, and data compression. Power Query brings the benefit of good performance at the data source using a technique called Query Folding.

Query folding 

The primary function of query folding within Power Query Editor is to help increase the performance of your Power BI reports and eliminating performance issues. We may define, Query folding as the process by which the transformations and edits that are made in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you are actively making transformations. The primary purpose of implementing the process of query folding is to ensure that these transformations can take place in the original data source server and do not overwhelm Power BI computing resources.

We can use Power Query to load data into Power BI. Thereby, using Power Query Editor we make further transformations to your data, such as renaming or deleting columns, appending, parsing, filtering, or grouping your data.

Benefits to Query Folding

• Firstly, it helps brings more efficiency in data refreshes and incremental refreshes
• Also, it helps build automatic compatibility with DirectQuery and Dual storage modes

Note – In case the View Native Query option is unavailable then that query folding is not possible. Then you will have to work backward in the Applied Steps area until you reach the step in which View Native Query is available. Thereby, this process helps to reveal the native query that is used to transform the dataset.

One of the guideline to translate a transformation into a Select SQL statement includes operators and clauses such as GROUP BY, SORT BY, WHERE, UNION ALL, and JOIN, you can use query folding.

Query folding is one of the options to optimize performance when retrieving, importing, and preparing data, another option is query diagnostics.

Query Diagnostics  

The tool that we can use to study query performance is query diagnostics. It allows determining the obstructions that exist while loading and transforming your data, refreshing your data in Power Query, running SQL statements in Query Editor.

For accessing query diagnostics in Power Query Editor use the following steps –

• First, go to Tools in the Home ribbon
• Then, once you are ready to begin transforming your data or making other edits in Power Query Editor
• Next, select Start Diagnostics on the Session Diagnostics tab.
• Lastly, when you are finished, ensure that you select Stop Diagnostics.

In general, this tool is helpful when you want to analyze performance on the Power Query side for tasks such as loading datasets, running data refreshes, or running other transformative tasks. 

Other techniques to optimize performance  

We shall now elaborate other ways to optimize query performance in Power BI include –

• Process as much data as possible in the original data source
• Use native SQL queries
• Separate date and time, if bound together