skip to content

In Power Query, what is query folding and which steps typically break it?

level: middleimportance: must knowfreq 80%

answer

  1. the source can do the work itself
  2. check whether the native query is still available
  3. an index column ends the translation
  4. everything after the break runs in the mashup engine
  5. incremental refresh silently depends on it

basics

~20 s

Query folding is Power Query translating Applied Steps into a single native query the source executes, so filtering and aggregation happen at the source. Steps M cannot translate — an index column, Table.Buffer, cross-source merges — end folding, and everything after runs locally.

solid answer

~50 s

Power Query tries to compile your Applied Steps into one native query — typically SQL — that the data source runs, instead of pulling rows and transforming them in the local mashup engine. Removing columns, filtering rows, renaming, grouping, joining two tables from the same source and simple type changes usually fold. Adding an index column, `Table.Buffer`, merging across different sources, and custom M with no source equivalent do not. Folding stops at the first untranslatable step: everything up to it runs at the source, everything after it runs in the mashup engine (on your machine in Desktop, on the gateway or in the cloud on refresh) over whatever rows the source returned. That makes folding the biggest refresh-performance lever in Power BI — and Power BI's incremental refresh depends on the `RangeStart`/`RangeEnd` filter folding, or every partition scans the whole table. Check with **View Native Query** on a step, or the step folding indicators in recent Power BI Desktop builds.

code

powerquery · 8 lines
powerquery
let
    Source = Sql.Database("srv", "sales"),
    Orders = Source{[Schema="dbo",Item="Orders"]}[Data],
    Filtered = Table.SelectRows(Orders, each [OrderDate] >= #date(2024,1,1)),
    Kept = Table.SelectColumns(Filtered, {"OrderID","CustomerID","Amount"}),
    Grouped = Table.Group(Kept, {"CustomerID"}, {{"Total", each List.Sum([Amount]), type number}})
in
    Grouped

go deeper

for a junior

Know that Power Query can translate steps into a query the source runs itself, and that filtering and removing columns early is what makes that pay off.

for a middle

Explain which steps translate and which do not, that folding stops permanently at the first untranslatable step, and how to check with View Native Query.

for a senior

Diagnose a refresh that regressed because folding broke, reorder or restructure steps to restore it, and connect folding to gateway memory and incremental refresh partitioning.

for a principal

Set the boundary: how much shaping is allowed in Power Query at all, what belongs in source-side views, and how folding-safe patterns are standardised across many reports.

## What folding is Power Query has two possible execution strategies for any step. It can fetch rows from the source and transform them itself in the **mashup engine** — the local evaluation engine that runs in Power BI Desktop, in the on-premises data gateway, or in the cloud service during a refresh. Or, when the source is a query engine capable of doing that work, it can *translate* the step into the source's own language and let the source do it. That translation is called **query folding**. For a SQL source, folding means your Applied Steps are compiled into one `SELECT` statement. "Remove columns" becomes a shorter select list, "Filter rows" becomes a `WHERE` clause, "Group by" becomes `GROUP BY` with aggregates, "Merge" against another table on the same connection becomes a `JOIN`, "Sort" becomes `ORDER BY`. The database does the scanning and reduction with its indexes and statistics, and only the reduced result crosses the wire. ## Why it matters more than anything else in Power Query A folded query that filters two years of a billion-row table to one region reads and ships the region. The same steps unfolded read the entire table across the network, materialise it in the mashup engine's memory, and *then* apply the filter. Refresh duration, network volume, gateway memory pressure and load on the source database all move by an order of magnitude on the same visible steps. Folding is also a hard prerequisite for **incremental refresh** in Power BI. Incremental refresh partitions a table by date using two required parameters named `RangeStart` and `RangeEnd`, and it works by refreshing only the recent partitions. If the filter on those parameters does not fold, each partition's query pulls the whole table before filtering locally — you get all the complexity of partitioning and none of the benefit, and refreshes get slower rather than faster. ## What folds and what does not **Usually folds** on a relational source: removing/renaming/reordering columns, filtering rows, distinct, group-by with standard aggregates, joins between tables on the same source connection, appends within the same source, simple type changes, and many basic column calculations. **Generally does not fold**: adding an index column (no SQL equivalent for row position over an unordered set), `Table.Buffer` (which explicitly says "materialise this here and now"), merging or appending across *different* sources, custom columns using M functions with no source counterpart, and many advanced text/date functions depending on the connector. **Never folds at all**: file connectors. A CSV, an Excel workbook or a JSON file has no query engine to push work into; the whole file is read and everything happens in the mashup engine. "Should I filter my CSV early so it folds?" has no meaning — filtering early is still good hygiene for memory, but nothing is being pushed anywhere. A native SQL statement typed into the connector also blocks folding on top of it for many connectors, because Power Query cannot safely rewrite hand-written SQL; some connectors expose an option on `Value.NativeQuery` that permits folding on top of a native statement. ## Step order is a real lever Because folding stops at the first untranslatable step and never resumes, order matters enormously. Put every folding-friendly reduction — filters, column removal, group-by — **before** the index column or the buffer or the exotic custom column. The same set of steps in the other order can fold entirely or not at all. ## How to check Right-clicking a step offers **View Native Query**, which shows the source query as of that step; when the option is greyed out, folding has already stopped at or before that step. Recent Power BI Desktop versions also show step folding indicators in the Applied Steps list, marking which steps still fold. **Query Diagnostics** records what the engine actually did during an evaluation, including the queries sent to the source. And the most honest check is at the source itself: watch what query the database receives. ## Restoring folding Move non-folding steps to the end. Replace an index-based trick with something the source can express. Push the hard part upstream into a database view or an upstream model so Power Query only selects from it. Split a cross-source merge so each source is reduced in its own folded query before they meet. And avoid `Table.Buffer` unless you are deliberately preventing repeated evaluation — it is a folding killer, not a performance switch. One caution on vocabulary: folding is about Power Query pushing work to the *data source* at refresh time. It has nothing to do with DAX filter context, which is about how a measure is evaluated inside the model at query time. The two are different layers and are frequently confused in interviews.

  • How do you confirm whether a specific step still folds?
    Right-click the step and look for **View Native Query** — if it renders a query, everything up to that step folded; if it is greyed out, folding stopped at or before it. Recent Power BI Desktop builds also mark folding status per step in Applied Steps. For proof rather than inference, run Query Diagnostics or watch the query arriving at the database.
  • Why does incremental refresh in Power BI get slower when the RangeStart filter stops folding?
    Incremental refresh runs one query per partition, each filtered to its date window by the `RangeStart`/`RangeEnd` parameters. If that filter does not fold, every partition query pulls the entire table and filters locally — so instead of one full scan you now perform one per partition, plus the mashup engine's memory cost each time.
  • Does Table.Buffer make a refresh faster?
    Rarely, and it always ends folding. `Table.Buffer` materialises a table in memory at that point, which helps only in narrow cases such as preventing a repeatedly re-evaluated small table from being re-fetched, or freezing a sort before an index. Applied to a large source table it converts a pushed-down query into a full extract plus local processing.
  • If Power Query cannot fold a needed transformation, what is the next best move?
    Push it upstream. Create a view (or an upstream model) at the source that performs the transformation, and point Power Query at that view so it only selects and filters. This keeps the work in the engine best equipped for it, keeps the logic under source control, and lets every consumer inherit it rather than each report reinventing the step.

Folding is sending your shopping list to a shop that picks the items for you; when it breaks, the shop ships you the entire warehouse and you pick on your kitchen floor.

saying these in an interview costs you the question

  • Believes folding also happens on CSV or Excel sources
  • Thinks step order has no effect on folding
  • Assumes a greyed-out native query is just a UI limitation
  • Claims Table.Buffer speeds up refresh
  • Confuses query folding with DAX filter context

context

resolving links…