Query folding

What is query folding?

Query folding is the ability of a Power Query query to turn your transformation steps into a single query that the data source runs on its own. When you filter rows, remove columns or sort a table, Power Query tries to translate those steps into the native language of the source, usually SQL, and hand the work to the database instead of doing it locally.

The Source step already reads the data in the source's own language, and folding applies to every step you add after it. Power Query looks at that chain, works out which steps the source can run, and combines them into one request.

Why folding matters for refresh performance

Say your source table holds ten million order rows, but a report only needs orders from this year. If the date filter folds, Power Query sends a WHERE clause to the database and gets back a few hundred thousand rows. If it does not fold, Power Query pulls all ten million rows across the network and throws most of them away in memory.

Every fold is one less pass the local engine has to make and far less data crossing the wire. On large tables that is often the gap between a refresh that finishes in seconds and a Power BI model that times out mid-refresh.

A query lands in one of three states. Full folding pushes every step to the source and the engine barely touches the data. Partial folding pushes some steps and runs the rest locally. No folding means the source hands over raw data and Power Query does all the shaping itself.

Which sources and steps fold

Folding depends on the source having a query engine that can run the translated request. Structured, relational sources are where it works best: SQL Server, Azure SQL, Snowflake, PostgreSQL, or an OData feed. Flat files such as CSV and Excel have no engine to push work to, so nothing folds and every transform runs in Power Query.

Steps that map cleanly to SQL tend to fold. Filtering rows becomes a WHERE clause, choosing and removing columns becomes the SELECT list, and sorting becomes ORDER BY. Aggregations and merges can fold too, as long as the source can express the result as a single statement.

Folding breaks the moment you add a step the source cannot translate. Common culprits:

• A transform with no source equivalent, such as Capitalize Each Word (the M function Text.Proper), which never folds.

• Row-by-row logic or a custom M function the database has no way to run.

• Keeping the bottom rows of a table (Table.LastN), which the source cannot express without a sort to define what last means.

• A native SQL query added with the Value.NativeQuery function, which by default stops folding for every step after it.

Once a step breaks folding, every step after it runs locally too, even simple ones. That is why the order of steps changes performance so much: filter and trim columns early, and push heavy custom logic to the end.

How you write a step can also decide whether it folds. Keeping the last ten rows of a multimillion-row sales table with a keep bottom rows step does not fold, so every row travels to the engine before it trims to ten. Rewritten as sort descending then keep top ten, the same result folds into a single statement and the source returns only ten rows.

How to check whether a query folds

Folding is not a switch you toggle, so you check it rather than assume it. Power Query gives you a few ways to look.

• View Native Query. Right-click a step in the Applied Steps pane and pick View Native Query, called View Data Source Query for some connectors, to read the exact SQL Power Query will send. If the option is greyed out on a step and the source normally supports it, that step has broken folding.

• Query folding indicators. In Power Query Online, each step shows an icon for whether the query folds up to that point. The reading is not step-by-step: it describes the whole query so far, so everything up to the last folding icon still folds. States are folding, not folding, and might fold for connectors like ODBC and OData where it is decided at runtime.

• Query Diagnostics. This records the actual requests sent to the source, so you can confirm a single filtered query went out rather than a full table scan. A source-side trace, such as SQL Server Profiler, tells the same story from the database side.

Query folding in import mode and DirectQuery

How much folding matters depends on the storage mode of your model. In import mode, folding is optional. The data is copied into the model when it refreshes, and folding only decides how fast that refresh runs. You want it on large tables, but a small table that does not fold will still load.

In DirectQuery mode, folding is not optional. Every visual sends a live query to the source, so the whole Power Query has to fold into a single statement. A DirectQuery or Dual storage mode table that cannot fold will not work. A native query used there also has to be a plain SELECT, with no common table expressions or stored procedures.

Incremental refresh depends on the same thing. It only asks the source for one time period at a time if the RangeStart and RangeEnd date filter folds, and both parameters have to be the Date/Time data type for that to happen. When the filter does not fold, the engine pulls every row and trims locally, which defeats the point of setting it up.