How to Identify Query Folding Breakers in Power Query (With Real M Diagnostics and Fixes)

Light

A single Table.Buffer or custom M step can quietly break query folding in Power Query, pushing all data processing onto your local machine with zero warning from the interface. By the end of this article, you’ll be able to systematically detect folding breakers, trace their impact using real diagnostics, and apply targeted fixes that preserve folding for efficient data refresh at scale.

Why Query Folding Breaks — And Why the UI Won’t Save You

Most Power BI developers know that query folding is the difference between an efficient, source-pushed query and a slow, memory-hogging local transform. What’s less obvious is how easily folding breaks — and how quietly Power Query hides it. The “View Native Query” option vanishes the moment folding fails, but that’s a superficial signal. It only shows up for some connectors (not all), and it only reports on the step you select, not the downstream consequences. If you trust the UI alone, you will miss folding failures that happen mid-stream, especially once you add custom M logic or reference queries.

• Not all folding failures are obvious: Steps like adding an index column (Table.AddIndexColumn), invoking a custom function, or even reordering columns can silently break folding, depending on the data source.
• Downstream steps inherit the failure: Once a folding breaker hits, every subsequent step executes locally, compounding the performance hit on large datasets.
• Some connectors (like Excel, CSV) never fold: But with SQL Server, OData, or Direct Lake sources in Fabric, folding is the default — until you break it.

If you’ve ever wondered why a refresh runs quickly on a small preview but grinds to a halt at full scale, silent folding breakers are a prime suspect.

Real Diagnostics: Confirming Folding (or the Lack of It)

The only reliable way to know if your query folds — and where it stops — is to use diagnostics outside the superficial “View Native Query” check. Here’s how I approach it:

1. Use “View Native Query” — but Know Its Limits

This context menu item is only available for supported connectors and only for steps that fold. If it’s disabled, folding has broken at that step or earlier. But:

• Some steps never offer it, even if folding is intact (e.g., when using custom connectors).
• It tells you nothing about performance — only that the source can represent the logic in its native language at that step.

2. Query Diagnostics: The Only End-to-End Folding Proof

Since the Diagnostics tools (now stable in Desktop), I rely on them for real evidence. Here’s the method:

1. Go to Tools > Start Diagnostics before triggering a refresh of the query in Power Query Editor.
2. Let the refresh finish, then Stop Diagnostics. You’ll get a Diagnostics table in the Queries pane.
3. Filter for Data Source Query events — these capture the actual SQL or source query Power Query sent upstream.
4. Examine the folded query: If you see a single, complex SQL (or similar) query representing all your logic, folding is intact. If you see multiple source queries (or, worse, none for later steps), folding is broken and local evaluation is happening.

This approach works across sources, even those not supported by “View Native Query”.

3. “View Query Plan” — For the M-Obsessed

The (still lesser-known) “View Query Plan” feature in the Advanced Editor shows a representation of how Power Query intends to execute each step. While not as explicit as Diagnostics, it can help pinpoint which step triggered the fallback to local evaluation.

The Usual Suspects: Common Folding Breakers (and What to Do Instead)

Plenty of M steps are documented as folding breakers, but in practice, a handful account for most silent failures. Here’s where I see experienced developers trip up:

• Table.Buffer — Used to “improve performance” but universally breaks folding; it forces materialization of the previous step locally.
• Table.AddIndexColumn — Breaks folding for almost all SQL backends. Indexes are a local concept.
• Invoking custom functions — Passing a table row-by-row into a custom M function kills folding, as the engine can’t translate arbitrary logic to SQL or source code.
• Reference queries — Referencing a query that’s already broken folding inherits the failure, even if your current steps are foldable.
• Type changes after folding is lost — If you apply Table.TransformColumnTypes after folding is broken, that work happens locally — and can be expensive on large tables.

The less obvious pattern is that folding is all-or-nothing from the last breaking step forward. If you break folding at step 5, steps 6-20 are doomed to local evaluation, even if they’d otherwise fold.

How to Fix: Move, Replace, or Remove the Breaker

1. Push folding breakers as late as possible. If you must use Table.AddIndexColumn or similar, do it after all heavy filtering/joins are complete.
2. Replace with source-supporting logic where possible. For example, instead of adding a calculated column in Power Query, push the calculation into a SQL view or source query.
3. Avoid Table.Buffer unless you have a folding-breaking reason. It’s rarely needed. If you must buffer, buffer the smallest necessary subset, not the whole source table.
4. When invoking functions, use native M functions that can fold. Avoid custom logic until the end, or batch up logic so it operates on a single table, not row-by-row.

Worked Example: Chasing a Folding Breaker Through a Real Query

Take a Power Query pulling Fact_Sales from SQL Server. The source table is ~30M rows. The goal: filter for sales in the last year, add a running total, and join product details.

1. Start with a Source step: SQL Server table (Fact_Sales).
2. Filter on [SaleDate] for the last 12 months: Table.SelectRows — folds cleanly.
3. Add running total via List.Generate in a custom column (naively):

// Naive, folding-breaking running total
Table.AddColumn(
    PreviousStep,
    "RunningTotal",
    each List.Sum(
        List.FirstN(
            List.Sort(PreviousStep[Amount]),
            [Index]
        )
    )
)

This step breaks folding immediately. No SQL backend can represent row-by-row list logic. Every subsequent step — including your Table.Join with Dim_Product — now processes all 30M rows locally. This is a recipe for catastrophic refresh performance and out-of-memory errors.

Fix: Move all foldable steps before the custom logic. For running totals, if you can’t express it in SQL, consider materializing it upstream (e.g., via a SQL view), or accept that this step must be last and buffer/filter as aggressively as possible before it.

Checklist: What to Audit Before Finalizing a Query

• Step through every applied step, checking if “View Native Query” is enabled. If it’s not, folding is broken from there on.
• Run Query Diagnostics to confirm which query (if any) is sent to the source.
• Minimize or relocate folding-breaking steps. Never add Table.Buffer, Table.AddIndexColumn, or custom logic before major filters or joins.
• Reference only queries that themselves still fold. Check referenced queries, not just your own steps.
• Use source-side logic (SQL views, stored procedures) for complex calculations when possible.

Don’t trust the interface alone. The difference between a model that refreshes in minutes and one that times out is often a single folding breaker hiding mid-pipeline.