What Is Query Folding in Power BI?
Query folding in Power BI is the ability of Power Query to translate transformation steps into native source queries (e.g., SQL statements) that run on the data source server instead of locally in Power Query’s engine. It is the single most important performance optimization in Power Query, because it pushes filtering, aggregation, and joins to the server — reducing data transferred and refresh time.
How Does Query Folding Work?
When you add steps in Power Query (filter rows, select columns, group by, merge), Power Query checks whether the underlying data source connector can express that step as a native query. If it can, the step ‘folds’ — it becomes part of the SQL statement (or OData query, or other native request) sent to the server. If a step cannot fold, Power Query downloads the data up to the last folded step and processes the remaining steps locally.
- Steps that typically fold: Select Columns, Filter Rows, Sort, Group By, Merge (when both tables are from the same source), Remove Duplicates, Rename Columns.
- Steps that typically break folding: Add Custom Column (with M logic), Unpivot, Pivot, Merge with a local table, complex M functions.
How to Check If Query Folding Is Happening
Right-click any step in the Applied Steps pane. If ‘View Native Query’ is available (not greyed out), that step and all steps above it have folded to the source. The native query window shows the exact SQL (or equivalent) that Power Query sends to the server. If ‘View Native Query’ is greyed out, folding has broken at or before that step.
Why Is Query Folding Important?
- Performance: The server processes data far faster than the local Power Query engine, especially for large datasets.
- Reduced data transfer: Only the filtered, aggregated result is sent to Power BI — not the entire raw table.
- Incremental refresh: Power BI’s incremental refresh feature relies on query folding to partition data by date range at the source level.
How to Check and Preserve Query Folding in Power BI — Step-by-Step Tutorial
This tutorial shows you how to verify query folding on your Power Query steps and how to reorder transformations to preserve folding for better refresh performance. You will finish in 5 steps and under 10 minutes.
Steps
Step 1: Open Power Query and select a query
Click Home → Transform Data. Select a query connected to your SQL database in the Queries pane.
Step 2: Check folding on the last step
In the Applied Steps pane, right-click the last step. If ‘View Native Query’ is clickable, all steps have folded. Click it to see the SQL statement sent to the server.
Step 3: Identify where folding breaks
If ‘View Native Query’ is greyed out on the last step, work backwards: right-click earlier steps until you find the last one where it is available. The step immediately after that is where folding breaks.
Step 4: Reorder to preserve folding
Move foldable steps (filters, column selections) before non-foldable ones (custom columns, pivots). This maximizes the work done by the server. For example, filter rows before adding a custom M column, not after.
Step 5: Verify the improvement
After reordering, check ‘View Native Query’ again on the last foldable step — confirm the filter appears in the SQL WHERE clause. Refresh the dataset and compare load time.
