Power QueryExercises

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
    Labelled

Result

OrderIDOrderDateAmountLabel
A-22026-10-0235A-2 on 02 Oct

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Runs here on an in-memory table to show the order of steps; against a database, the first two steps are the ones that could fold.

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.

Lessons

More resources

Sources

Microsoft's official documentation for the features and functions used on this page.

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.