Query folding diagnosis: find the step that stops folding
Checklist · By Tahir Nas · Examples run in our simulator · Published · Updated
1. Is the source foldable at all?
Folding needs a source that can run queries: SQL Server, PostgreSQL, Oracle, many ODBC sources, some OData feeds. Files (CSV, Excel, JSON) and folders never fold, so slow file queries need different fixes (fewer columns, fewer files, staging).
2. Find the first non-folding step
In Power BI Desktop, step folding indicators show which steps fold. Otherwise, right-click each step from the top: while View Native Query is available, the step folds. The first step where it's greyed out is where local processing starts, and everything after it runs locally too.
3. Reorder so foldable work comes first
Move filters, column selection, renames and simple type changes above the first non-folding step. The shape you're aiming for:
let
Orders = #table({"OrderID", "OrderDate", "Status", "Amount", "Internal"}, {
{"A-1", #date(2026, 9, 30), "Complete", 20, "x"},
{"A-2", #date(2026, 10, 2), "Complete", 35, "y"},
{"A-3", #date(2026, 10, 3), "Cancelled", 12, "z"}
}),
// Foldable block: filter rows and columns first
Recent = Table.SelectRows(Orders, each [OrderDate] >= #date(2026, 10, 1) and [Status] = "Complete"),
Slim = Table.SelectColumns(Recent, {"OrderID", "OrderDate", "Amount"}),
// Non-foldable block last: custom logic on the small result
Labelled = Table.AddColumn(Slim, "Label", each Text.Format("#{0} on #{1}", {[OrderID], Date.ToText([OrderDate], [Format = "dd MMM"])}), type text)
in
LabelledResult
| OrderID | OrderDate | Amount | Label |
|---|---|---|---|
| A-2 | 2026-10-02 | 35 | A-2 on 02 Oct |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
4. Common folding breakers to look for
- An index column, or a custom column using functions the connector can't translate.
- Merging with a table from another source (a file, another server).
- Changing types in ways the source can't express, or text operations on large columns.
- Table.Buffer, which deliberately pulls the data into memory.
5. When to write native SQL
If a transformation can't fold and the table is large, a view in the database, or a native query, can do the work at the source. In Power Query that's Value.NativeQuery; with [EnableFolding = true] later steps may still fold on top of it in supported connectors. The simulator can't run it:
Value.NativeQuery(Sql.Database("sql.example.internal", "Sales"), "SELECT OrderID, Amount FROM dbo.Orders WHERE Status = 'Complete'", null, [EnableFolding = true])Not run by the simulator
Value.NativeQuery is not supported in the Power Query Exercises simulator. See the compatibility page for the supported subset. This code is shown for real hosts only; see the simulator compatibility page.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
6. Incremental refresh depends on folding
Power BI incremental refresh filters on RangeStart and RangeEnd parameters. If that date filter doesn't fold, every refresh reads the whole table, and the feature saves nothing. Put the date filter directly after the source navigation step.
Related pages
Lessons
More resources
Sources
Microsoft's official documentation for the features and functions used on this page.
- Overview of query evaluation and query folding in Power Query (Concept, Microsoft Learn)
- Step folding indicators (Concept, Microsoft Learn)
- Query diagnostics (Concept, Microsoft Learn)
- Table.SelectRows (Function reference, Microsoft Learn)
- Table.SelectColumns (Function reference, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
- Text.Format (Function reference, Microsoft Learn)
- Date.ToText (Function reference, Microsoft Learn)
- Value.NativeQuery (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.