Power BI query folding: let the data source do the heavy work
Query folding in Power BI is the process in which Power Query translates your transformation steps into operations that the data source can run. When steps fold, the source filters and shapes the data before it travels. When they do not, the Power Query engine does that work itself. This guide explains the mechanism, the three outcomes and the ways to check them.
- Work pushed to the source
- Full, partial or none
- Check what folds
Last updated
What is Power BI query folding in plain terms?
Every query in Power Query has an M script behind it. Microsoft compares that script to a recipe that describes how to prepare your data. The Applied steps pane shows the same recipe as a list of named steps.
During evaluation, Power Query tries to hand steps to the data source. Microsoft calls this optimization query folding. It translates supported transformations into the native language of the source, such as SQL, and combines them into a single source query.
The goal is simple. Power Query pushes as much of the evaluation as possible to a source that can compute it. Any transformation that cannot fold then runs in the Power Query engine, which Microsoft also calls the mashup engine.
The three outcomes of Power BI query folding
Microsoft names three possible outcomes, and they depend on how the query is structured.
| Outcome | At the data source | In the Power Query engine |
|---|---|---|
| Full folding | All query transformations | Minimal processing |
| Partial folding | The steps Power Query can translate | The remaining steps |
| No folding | Returns the requested source data | All required transformations |
| SQL Server source | Commonly supports folding | Only the steps that cannot fold |
| CSV or Excel file | No query engine, so no folding | Every transformation |
Which data sources support folding?
Support depends on three things: the connector, the data source and the transformations in the query. Microsoft states that connectors for structured sources with query-processing capabilities commonly support folding. It names SQL Server and OData feeds as examples.
Files are different. Sources without a query engine, such as CSV and Excel files, do not support query folding. Therefore Power Query has to read the data and perform each step itself.
The Source step has a special role. Data source functions query the source in its native language. For the transformations that follow, the engine attempts folding only when both the connector and the transformation support it.
How a folded query runs, in four steps
This sequence is a short version of the evaluation flow that Microsoft describes.
Read the script
First the Power Query engine receives the M script, along with the credentials and the privacy levels of the sources.
Ask about capabilities
Next the folding mechanism sends metadata requests to learn what the source can do and how its tables look.
Send one source query
Then Power Query sends a request to the source, and the source returns the data it prepared.
Finish and load
Finally the engine performs any steps that could not fold and loads the result to its destination.
Why does Power BI query folding matter for refresh times?
Microsoft states that folding often gives faster query execution than the alternative. Without it, Power Query extracts all the required data from the source and runs every transformation in its own engine.
A database usually has more processing resources than the machine that runs the refresh. So when the source filters rows and removes columns first, less data travels. Microsoft notes that this can reduce query load times.
Lazy evaluation helps as well. Power Query works out which transformations the final output really needs and skips the others. The engine may also reorder steps during optimization. However, privacy levels between several sources can affect how a query is evaluated. For hands-on help with slow queries, see Power Query consulting.
Three ways to check whether steps fold
Microsoft documents these tools, though their availability depends on the product and the connector.
Native query view
When available, View Native Query or View data source query shows the request that Power Query sends to the source.
Folding indicators
In the online editor, indicators beside the applied steps show which steps fold. Microsoft limits this feature to the online editor.
Query plan
Also in Power Query Online, the query plan shows which operations run at the source and which run in the engine.
Power BI query folding: frequent questions
How do I know whether Power BI query folding is working?
Look at the request that Power Query sends. When the connector offers it, the View Native Query option shows the source query for a step. If the query includes your filters and column choices, those steps fold. In the online editor, folding indicators and the query plan give the same insight.
Does Power BI folding work with Excel or CSV files?
No. Microsoft states that sources without a query engine, such as CSV and Excel files, do not support query folding. Power Query then processes all the transformations itself. If such files grow large, consider loading them into a database first, so that the source can do the work.
What does partial folding mean in Power BI?
It means the work is split. The data source processes the transformations that Power Query can translate into its native query language. After that, the Power Query engine processes the remaining ones. So the order of your steps matters, and steps that fold should come early where possible.
Should I write the M script by hand to improve folding?
Usually not. Microsoft recommends that you let the Power Query editor create all or most of the M script, because the editor ensures valid code. You can still edit the script manually in the Advanced Editor. However, check the native query afterward to confirm that the steps still fold.
Can Power BI Agency speed up our slow refreshes?
Yes. One of our consultants reviews your queries step by step, checks which ones reach the source and reorders or rewrites the rest. We work remotely in your own Microsoft tenant and agree the scope in a written quote. Power BI Agency is a division of Sataware Technologies, a Microsoft Solutions Partner, and is not part of Microsoft.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.