Query Diagnostics & Query Folding in Power Query

When Power BI reports take tens of minutes to refresh or when visual interactions feel slow, the root cause is frequently an un-optimized Power Query ETL transformation. To debug data extraction performance, intermediate developers utilize Query Diagnostics and master Query Folding.

1. What is Query Folding? (The Silver Bullet of ETL)

Query Folding is the ability of Power Query to translate user transformation steps (filtering, grouping, joining, column selection) into a single native query (such as SQL) and execute it on the source database server:

Why Query Folding is Critical:

• The database server (SQL Server, Oracle, Snowflake) performs the heavy filtering and aggregation using its indexed hardware.
• Only the compact, aggregated final result is sent over the network to Power BI.
• Enables Incremental Refresh in the Power BI Service!

2. Checking if a Step is Folding

1. 1
   In Power Query Editor, right-click on the last applied step in the Applied Steps list.
2. 2
   Inspect the View Native Query option:

• Enabled (Clickable): The step folds! You can click it to view the exact SQL statement generated.
• Disabled (Grayed Out): Query folding has broken! Power Query was forced to download the entire un-filtered raw dataset into local memory to evaluate that step.

3. Running Query Diagnostics

To trace which exact step is taking the most CPU time or network I/O:

1. 1
   In Power Query Editor, go to the Tools ribbon tab.
2. 2
   Click Start Diagnostics.
3. 3
   Perform the query refresh.
4. 4
   Click Stop Diagnostics.
5. 5
   Power Query automatically generates diagnostic result tables detailing query duration, resource usage, and data source activity.