Query Folding
Power BI · Advanced Features
Theory
Query folding is Power Query's ability to translate your applied steps into a single query in the source's own language, usually SQL, so the source does the work and only the result is sent to Power BI. Steps like filtering rows, removing columns, renaming, grouping and joining typically fold against relational databases. For example, connecting to SQL Server, keeping five columns and filtering OrderDate to 2024 can fold into one SELECT ... WHERE OrderDate >= '2024-01-01' statement instead of downloading the whole table.
Folding stops at the first step the source cannot express, and every later step then runs in the Power Query engine. Flat files such as CSV and Excel never fold. You can check by right-clicking a step: if View Native Query is available, that step folds. Putting foldable steps first and complex ones last keeps as much work as possible in the source.
Why we use it
Folding makes refresh faster and lighter, because the database filters and aggregates before data travels. It is also required for incremental refresh to work efficiently and for DirectQuery to work at all.
When to use it
Care about folding whenever the source is a database and tables are large. For small files it matters little. If a needed step breaks folding, move it later, push the logic into a source view, or use a native query.
How it works
- Power Query evaluates the steps
- Can the source express the next step? Yes: Add it to the native query. No: Stop folding here.
- Steps after the break run in the Power Query engine
- Source returns only the needed rows and columns
- Faster refresh, less data moved
Syntax
Power Query → right-click a step → View Native Query (greyed out usually means no folding)
Fold-friendly: filter rows, remove/select columns, rename, group by, merge, sort
Often break folding: adding an index column, some custom M functions, combining different sourcesExample
Folding-friendly order for SQL Server:
1. Source = Sql.Database("sql-prod-01", "SalesDB")
2. Navigation → dbo.Sales
3. Removed Other Columns (folds)
4. Filtered Rows: OrderDate >= 2024-01-01 (folds)
5. Grouped Rows by Region (folds)
6. Added Index column (breaks folding; keep it last)
Right-click step 5 → View Native Query shows one SELECT ... WHERE ... GROUP BY.Key points
- Folding pushes steps to the source as native SQL
- Flat files (CSV, Excel) do not fold
- Folding stops at the first non-foldable step
- View Native Query is a quick check for a step
- Required for efficient incremental refresh and for DirectQuery