Power QueryExercises

Query folding (concept)

Explain query folding and recognise steps that tend to break it.

~9 min read · intermediate · Module 19: Refresh robustness and performance concepts

By Tahir Nas · Examples run in our simulator · Published · Updated

This is a concept lesson. The practice simulator doesn't run this feature; the explanation describes how real Power Query hosts behave, and any code the simulator can't run is labelled. See the compatibility page.

Before this: Applied steps are a recipe, not a one-off edit, Step order matters

When a query reads from a database, Power Query tries to translate your steps into the source's own language (usually SQL) so the database does the work and sends back only the result. That translation is called query folding.

Why it matters

A filter that folds asks the database for 1,000 rows. The same filter without folding downloads 10 million rows and filters them on your machine. Refresh time, memory and network use can differ by orders of magnitude.

What the steps look like

Against a database, the source step would be a connector call. The simulator has no database, so it says so:

let
    Source = Sql.Database("sql.example.internal", "Sales"),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data]
in
    Orders

Not run by the simulator

Sql.Database 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.

The transformations that follow are ordinary M, and their order is what decides how much can fold. The same steps on an in-memory table show the shape of a fold-friendly query: filter and choose columns first, then do things the source may not understand (here, an index):

let
    Orders = #table({"OrderID", "Region", "Status", "Amount", "Notes"}, {
        {"A-1", "North", "Complete", 20, "gift"},
        {"A-2", "South", "Cancelled", 35, null},
        {"A-3", "North", "Complete", 12, null}
    }),
    // Likely to fold: filters and column selection
    Complete = Table.SelectRows(Orders, each [Status] = "Complete"),
    Slim = Table.SelectColumns(Complete, {"OrderID", "Region", "Amount"}),
    // Often stops folding: an index has no simple SQL equivalent
    Numbered = Table.AddIndexColumn(Slim, "Line", 1, 1, Int64.Type)
in
    Numbered

Result

OrderIDRegionAmountLine
A-1North201
A-3North122

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

Steps that usually fold

  • Choosing and removing columns
  • Filtering rows on simple conditions
  • Simple renames and type changes
  • Sorting, Group By, and joins between tables from the same database

Steps that often stop folding

  • Adding an index column
  • Custom functions or M expressions the source can't translate
  • Merging tables from different sources
  • Some text and date transformations, depending on the connector

Once a step doesn't fold, the steps after it run locally too. That's why the order matters: put foldable steps first.

Where to find this in Excel

Excel's editor has no folding indicators: Microsoft documents step folding indicators for Power Query Online only. In Excel you reason about folding from the steps themselves.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Applied steps (Microsoft Learn).

Common mistakes

  • Adding an index or custom column before the filters, so the filters run locally.
  • Assuming file sources (CSV, Excel) fold; they don't, because a file can't run a query.
  • Confusing folding with combining steps in the Applied Steps pane.

Excel, Power BI and the simulator

  • Power BI Desktop: right-click a step; if View Native Query is enabled, that step folds. Newer versions show step folding indicators next to each step.
  • Excel offers View Native Query for some database sources as well; folding indicators are mainly found in Power BI Desktop and Power Query Online.
  • The simulator raises UnsupportedFeature for database connectors (Sql.Database, Odbc and others) and never folds.

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Refresh robustness quiz