Query Folding in Power BI: Why Some Refreshes Are Fast and Some Aren't

Two students can build what looks like the same query against the same SQL database — same filters, same removed columns, same renamed fields — and end up with wildly different refresh times. One refreshes in a few seconds. The other pulls the entire source table into memory and chews through it step by step. The difference almost always comes down to query folding, and it's one of those mechanics that's invisible until you know to look for it — which is exactly why PL-300 asks about it directly in the "Prepare the data" domain.

What query folding actually is

Every step you add in Power Query Editor — filter rows, remove a column, rename a field, group by — gets recorded as a line of M code. When you hit refresh, Power Query has two ways to execute that M code. It can pull the raw data out of the source first and then run each transformation itself, row by row, inside its own engine. Or, if the source supports it, it can translate your M steps into the source's native query language — SQL, for a relational database — and send that single, pre-filtered, pre-shaped query to the source. The source does the work and hands back only the result.

That second path is query folding. The name describes what happens mechanically: each M step "folds" into the native query being built, so by the time Power Query actually asks the source for data, it's asking for exactly the rows and columns it needs — not the whole table.

Why it matters for refresh speed

The performance gap isn't subtle. If folding is working, a filter on a billion-row sales table might mean the database does an indexed lookup and returns ten thousand rows. If folding breaks before that filter, Power Query has to pull a meaningful slice — or the entire table — across the network into its own engine and filter it there, row by row, with none of the indexes or query optimizer the source database spent years building. On a small sample table in a course exercise this difference is invisible. On a production refresh against a real warehouse, it's the difference between a two-minute refresh and one that times out.

It matters even more in DirectQuery. In Import mode, a non-folding step just makes your refresh slower. In DirectQuery, every visual on the report fires a query against the source live — if your query doesn't fold, you're not just slowing a scheduled refresh, you're slowing down every single interaction a report user makes.

How to check whether a step folds

Right-click any step in the Applied Steps pane and look for View Native Query. If it's available (not greyed out), that step — and everything before it — is folding; clicking it shows you the actual SQL Power Query generated. The moment that option greys out on a later step, folding has broken at that point in the sequence, and everything from there onward runs locally in the Power Query engine regardless of what happens afterward.

This is also why step order matters. Folding isn't a property of the whole query — it's a chain that holds until something breaks it. Put your filters, column removals, and renames early, before anything that might not fold, and you keep the source doing as much of the work as possible for as long as possible.

What breaks folding

Not every data source supports folding at all — it depends entirely on whether Power Query knows how to translate M into that source's native query language. Relational sources like SQL Server, Azure SQL, Oracle, PostgreSQL, and Dataverse generally fold well. Flat file sources — Excel workbooks, CSV, plain text — can't fold at all, because there's no query engine on the other end to push logic into; Power Query always has to load the whole file and transform it locally, full stop.

Even against a source that supports folding, specific steps commonly break the chain:

• Custom columns using M functions with no SQL equivalent — anything built with Table.AddColumn that calls a function the source can't express (custom text parsing logic, certain date/time functions, invoked custom functions).
• Changing a column's data type to something the source can't natively represent, in some combinations.
• Merging queries from two different data source types (a SQL table merged with a SharePoint list, for example) — folding can't span two different native query languages.
• Adding an index column (Table.AddIndexColumn) — there's no SQL equivalent for "row number in this exact order" in most dialects, so this reliably breaks folding.
• Table.Buffer() — this one's worth knowing specifically because it's intentional. Table.Buffer forces Power Query to load a table into memory at that point, which deliberately stops folding from that step onward. It exists to make query evaluation order deterministic (useful when a source might return different results on repeated reads), not to speed anything up — using it without knowing this trade-off is a common way folding breaks by accident.

// This step breaks folding — custom row-by-row logic
// has no SQL translation:
AddCustomColumn = Table.AddColumn(
    PreviousStep,
    "Tier",
    each if [SalesAmount] > 10000 then "Enterprise"
         else if [SalesAmount] > 1000 then "Mid-Market"
         else "SMB"
)

A column like this is often unavoidable in a real model — but if you need it, add it as late as possible in your query, after every foldable transformation, so you lose folding only for the one step that needs it instead of everything downstream of it too.

A practical approach for real projects

When I'm reviewing a slow refresh for a student or a client, the fix is almost always reordering: move every filter, column removal, and rename to the top of the query, before any custom columns or merges, then check View Native Query step by step until I find exactly where it breaks. Sometimes the non-folding step can be eliminated entirely — a custom column that could instead be a measure in the model doesn't need to exist in Power Query at all, and moving logic from M into DAX sidesteps the folding question completely.

What PL-300 expects you to know

The exam objective is specific: "Identify when query folding occurs and why it matters for refresh speed," under the Prepare the data domain. In practice this shows up as a scenario question — a described refresh taking too long, or a DirectQuery report responding slowly — where the correct answer is to identify a non-folding step as the cause, not to suggest more compute or a different visual. You won't be asked to recite SQL translations; you're expected to recognize what breaks folding and know that step order and data source type are the levers that control it.