Query folding is the Power BI and Power Query mechanism by which M language transformations are translated into queries native to the source system — SQL for relational databases, OData queries for SharePoint and Dynamics, or API-specific queries — and pushed to the source for execution, rather than being executed locally by the Power Query engine after downloading the full source dataset. The distinction has major practical consequences: a Power Query step that folds to the source database allows the database’s query engine to filter, aggregate, and transform millions of rows before sending the result to Power BI; a step that does not fold causes Power BI to download the entire source table first and then apply the transformation locally in the mashup engine — which is slow, memory-intensive, and defeats much of the purpose of having a capable source database.
How It Works
Power Query’s M engine attempts to fold each transformation step to the source connector. For SQL Server, Oracle, Snowflake, and other ODBC-compatible sources, the M engine translates M transformations into SQL: a filter step (“keep only rows where Entity = ‘UAE'”) becomes a WHERE clause; a column removal step becomes a SELECT of specific columns; a join step becomes a JOIN clause. The M engine accumulates these translated expressions across consecutive steps and sends a single, optimised SQL query to the source. The query plan — what was folded versus what was applied locally — is visible in Power Query Editor through “View Native Query.”
Folding breaks when a transformation cannot be translated to the source language — M operations with no SQL equivalent, custom functions, operations on data that has already been downloaded from a different source. Once folding breaks, all subsequent steps in the M query execute locally regardless of whether they could individually be translated to SQL. The folding chain is preserved only through steps that are all translatable.
Design Decisions and Trade-offs
Query folding is a critical design consideration for Power BI Import mode datasets refreshing from large source tables. A finance GL table with 100 million rows must be filtered at the source — by date range, by entity, by account type — before Power BI downloads it. If the filter step does not fold, the refresh downloads all 100 million rows and then applies the filter locally, producing a refresh that is slow, memory-intensive, and may exceed capacity limits. Every filter, column selection, and simple transformation in a Power Query against a large relational source should be designed to fold.
Common Implementation Errors
The specific query folding failure that most degrades finance dataset refresh performance is adding a custom column or applying a function before a filter step in the M query. In Power Query, steps are applied in sequence; once a step breaks folding, all subsequent steps execute locally. A developer adds a custom column (step 4) that applies a text transformation to a column value — which cannot be folded — and then adds a filter (step 5) that should reduce the dataset to 10% of its rows. Because the custom column in step 4 broke the folding chain, the filter in step 5 also executes locally — after all rows have already been downloaded. Reordering steps so that all foldable filters and column selections come before any non-foldable transformations restores folding for the filter steps, minimising the data downloaded before the non-foldable step is applied.
How Loop Wise Solutions Designs for This
We review the native query output for every Power Query step against large source tables during model development — confirming which steps fold and restructuring the query order to maximise the data reduction achieved before folding breaks. For finance datasets where the source is a large ERP data extract, we design the query to apply all date range, entity, and account filters as the first steps in the M chain, confirmed to fold before any non-foldable transformation is introduced.
Answers before you ask.
The mechanism by which Power Query (M) transformations are translated into native source-system queries — SQL for databases, OData for APIs — and executed at the source. When folding occurs, the source performs the filtering and transformation and returns only the result, rather than Power BI pulling everything and processing locally.
Because when folding occurs, the source does the heavy lifting and sends only the needed data, which is far more efficient. When folding breaks, Power BI downloads the full dataset and processes it locally — slow and memory-heavy on large sources. Preserving folding is one of the most important levers for refresh performance and data volume control.
Transformation steps that have no native source-query equivalent — certain custom M functions, some column operations, or steps applied after a non-foldable step. Once folding breaks at a step, all subsequent steps run locally. Ordering foldable steps first and avoiding non-foldable operations where possible keeps as much work as possible at the source.
In Power Query, the option to view the native query indicates a step still folds; where it is unavailable, folding has stopped at that point. Checking this while building transformations reveals where processing shifts from the source to local. Diagnosing folding is essential when a refresh is unexpectedly slow or pulling excessive data.