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.
