Historical Context & Motivation
The challenge of efficiently transforming data before analysis has been a central concern in data engineering for decades. In the early days of business intelligence, ETL pipelines were hand-coded in SQL or proprietary scripting languages, requiring developers to manage every detail of extraction, transformation, and loading. Microsoft recognized that this workflow was a bottleneck—especially for analysts who were not SQL experts—and began embedding data-shaping tools directly into end-user products. The evolution from rudimentary import wizards in Excel to the fully featured Power Query engine represents a paradigm shift: a functional, lazy-evaluation query language accessible through a graphical interface, with the critical optimization of query folding operating behind the scenes.
The fundamental question this lesson addresses is deceptively simple: when you click buttons in the Power Query editor, what exactly happens under the hood, and why does it matter whether those transformations execute on the data source or in the local engine? Understanding the interplay between applied steps and query folding is essential for building Power BI solutions that scale from thousands to millions of rows without degrading refresh times or overwhelming local memory.
Core Principles & Definitions
Power Query operates on a principle that will feel familiar to any computer science student who has studied functional programming or compiler optimization: transformations are specified declaratively as a chain of immutable expressions, and an evaluation engine decides how best to execute them. Every interaction in the graphical editor—filtering a column, changing a data type, merging two tables—generates a corresponding expression in the M language (formally, the Power Query Formula Language). These expressions are organized sequentially as applied steps, and each step references the output of the previous one, forming a directed acyclic pipeline analogous to a UNIX pipe chain or a Spark DAG.
Applied Steps
let...in binding that transforms the output of its predecessor. Steps are lazily evaluated—only the final result (or a preview sample) is materialized until refresh time.Query Folding
The M Language
let expression with named steps and an in clause that returns the final table. Because M is purely declarative, the engine has freedom to reorder or fold operations.Foldable vs. Non-Foldable Operations
Folding Indicators
View Native Query inspection.Visual Explanation — The Applied Steps Pipeline
Notice the structural analogy to query planning in relational databases: the Power Query engine acts as a query optimizer that pushes as many operations as possible to the data source, just as a database optimizer pushes predicates below join nodes in an execution plan. The green-bordered steps are conceptually equivalent to operators that can be translated to the native query dialect, while the red-bordered steps represent operators that must remain in the local execution context. As a computer science student, you can think of the fold boundary as the point at which the abstract syntax tree of your M expression can no longer be transpiled into the target language.
How Query Folding Works — The Engine Internals
Query folding is not a single algorithm but rather a family of source-specific translators embedded within each Power Query connector. When you connect to a SQL Server, the connector knows how to map a subset of M functions to T-SQL clauses. For an OData feed, the mapping targets OData query parameters like $filter and $select. The engine processes the M expression tree from the source step outward, greedily folding each subsequent step until it encounters an operation that the connector cannot translate. This greedy, prefix-based folding strategy has important implications for step ordering.
The Folding Decision Process
Internally, the engine maintains a representation of the cumulative folded query as it walks the step chain. At each step i, it attempts to extend the native query by appending the equivalent clause. If the attempt succeeds, the folded query is updated and the engine proceeds to step i + 1. If it fails, the engine materializes the result of the folded query up to step i − 1 and evaluates all remaining steps locally. Crucially, once folding breaks, it cannot resume for subsequent steps—even if those later steps would individually be foldable. This is the 'broken chain' property.
Fold() is the connector-specific translation function, and ∘ denotes composition. Steps sₖ₊₁ … sₙ execute locally in the mashup engine.Classifying Operations — Foldable vs. Non-Foldable
Not all Power Query transformations are created equal from a folding perspective. Whether an operation folds depends on two factors: the capability of the connector and the expressiveness of the target query language. A SQL Server connector can fold a wide range of operations because T-SQL is richly expressive, whereas a flat-file CSV connector supports no folding at all since there is no query engine on the other end. The following classification provides a general guide, though specific behavior can vary by connector version and data source.
A useful mental model from compiler theory: foldable operations are the subset of the M language that falls within the intersection grammar of M and SQL—operations expressible in both languages. When you use an M-only construct (like iterating over a list within each row using List.Transform), you step outside that intersection, and the translator can no longer produce equivalent SQL. The practical consequence is clear: to maximize folding, you should express your transformations using the simplest, most standard operations possible, deferring complex M-specific logic to the end of the step chain.
Worked Example — Optimizing a Query for Folding
Consider a scenario where you connect to a SQL Server database containing a Sales table with 50 million rows. You need to produce a summary showing total revenue by product category for the year 2024, with a custom column classifying categories as 'High' or 'Low' revenue. We will build the query in two ways—a naive ordering that breaks folding early, and an optimized ordering that maximizes it—then compare the results.
Sales table and records the following applied steps in order: (1) Source, (2) Add Custom Column — classifies each row using if [Revenue] > 10000 then "High" else "Low", (3) Filter Rows — keep only Year = 2024, (4) Remove Columns — drop unnecessary fields, (5) Group By Category — sum revenue. Because Step 2 uses a custom M conditional expression, folding breaks at Step 2. Steps 3–5, which are normally foldable, now execute locally on all 50 million rows.View Native Query. The option is grayed out, confirming this step does not fold. Right-click Step 1 (Source) and the native query is available—a simple SELECT * FROM Sales. The folding indicator on Step 2 shows a dashed line icon. The key insight: the custom column's M expression cannot be translated to T-SQL by the connector.SELECT [Category], SUM([Revenue]) AS [TotalRevenue] FROM [Sales] WHERE [Year] = 2024 GROUP BY [Category]. Only the aggregated result (perhaps 20 rows) crosses the network.View Native Query. The generated SQL confirms the filter, column selection, and aggregation are all pushed to the server. The folding indicator shows solid green icons on Steps 1–4 and a dashed icon on Step 5 only. The custom column now operates on just 20 rows instead of 50 million.let Source = Sql.Database("server", "db"), Sales = Source{[Schema="dbo",Item="Sales"]}[Data], FilteredRows = Table.SelectRows(Sales, each [Year] = 2024), RemovedCols = Table.SelectColumns(FilteredRows, {"Category", "Revenue"}), Grouped = Table.Group(RemovedCols, {"Category"}, {{"TotalRevenue", each List.Sum([Revenue]), type number}}), AddedCustom = Table.AddColumn(Grouped, "Tier", each if [TotalRevenue] > 100000 then "High" else "Low") in AddedCustom. Each let binding corresponds to one applied step, and the order directly controls where folding breaks.Strengths, Limitations & Tradeoffs of Query Folding
| Aspect | Strength | Limitation |
|---|---|---|
| Performance | Dramatically reduces data transfer and leverages source-side indexing, parallelism, and caching. Refresh times can drop from hours to seconds for large datasets. | Performance gains depend entirely on the source engine's optimization. A poorly indexed SQL table may not benefit much from folded queries. |
| Transparency | Folding indicators and View Native Query provide direct feedback. Users can inspect the exact SQL generated, aiding debugging and optimization. | Folding is 'all or nothing' per step prefix—partial folding within a single step is not exposed. Users may not realize a step broke the chain without checking indicators. |
| Expressiveness | M is highly expressive, allowing arbitrary transformations including list operations, web API calls, and custom functions. Users are not limited to what folds. | The most expressive M constructs are exactly the ones that do not fold. There is an inherent tension between transformation flexibility and folding efficiency. |
| Source Compatibility | Many enterprise connectors (SQL Server, Oracle, PostgreSQL, Azure Synapse, Snowflake) support robust folding. OData and some APIs support partial folding. | Flat files (CSV, Excel), web scraping, and many REST APIs have no query engine, so folding is impossible. JSON and XML sources have very limited support. |
| Maintenance | Applied steps are version-controlled via the M code and can be shared through Dataflows or deployment pipelines. | Step ordering for optimal folding must be manually maintained. Inserting a step in the wrong position can silently break the fold chain with no error—only degraded performance. |
Connection to Advanced Theory — Dataflows, Incremental Refresh & Beyond
The concepts of applied steps and query folding are foundational to several advanced Power BI features that rely on the engine's ability to generate targeted native queries. Understanding folding unlocks comprehension of these more sophisticated mechanisms, which are increasingly important in enterprise-scale deployments.
| Foundational Concept | Advanced Feature | Relationship |
|---|---|---|
| Applied Steps (sequential M bindings) | Power BI Dataflows | Dataflows externalize applied steps into a shared, cloud-hosted layer. Step chains become reusable ETL artifacts stored in Azure Data Lake, enabling cross-report consistency and centralized data prep. |
| Query Folding (predicate pushdown) | Incremental Refresh | Incremental refresh requires query folding to work: Power BI generates date-range predicates (WHERE Date >= @start AND Date < @end) that must fold to the source so only new/changed partitions are refreshed. |
| Fold Boundary Awareness | Composite Models & DirectQuery | In DirectQuery mode, every DAX query triggers a Power Query evaluation. If steps do not fold, DirectQuery becomes impractical because every user interaction would pull and transform the full dataset locally. |
| M Language Fluency | Custom Connectors & SDK | When building custom connectors with the Power Query SDK, developers implement the folding logic themselves, defining which M functions map to their API's query parameters. Understanding folding conceptually is prerequisite to implementing it programmatically. |
As Microsoft continues to develop Fabric and its unified analytics platform, the query folding paradigm is extending beyond traditional databases. Folding to Spark endpoints, Lakehouse SQL analytics, and even KQL (Kusto Query Language) clusters is becoming increasingly supported. The conceptual framework you have learned here—sequential declarative transformations with greedy prefix-based folding to a native query language—remains the invariant architecture across all these targets. Mastering this mental model now positions you to work effectively with any future connector or compute target that Microsoft introduces.
Practice Problems
Text.Combine(List.Transform({[FirstName], [LastName]}, Text.Upper), " "), (4) Filtered Rows — Status = 'Active', (5) Removed Columns — drop 10 unused columns, (6) Sorted Rows by LastName. Identify the fold boundary, propose an optimized step order, and state which steps would fold after your reordering.Shipments table containing 200 million rows. You need to implement incremental refresh that processes only the last 7 days of data on each refresh. The business also requires an unpivoted view of delivery metrics. Explain why query folding is a hard requirement for incremental refresh, describe where you would place the unpivot step in the applied steps chain, and discuss what would happen if the unpivot were placed before the date filter.Lesson Summary
Applied steps are the ordered sequence of named M language expressions that form a Power Query transformation pipeline. Each step references its predecessor's output, creating a functional, immutable chain that is lazily evaluated at refresh time. Query folding is the optimization process by which the Power Query engine translates a maximal prefix of these steps into a single native query (typically SQL) that executes on the data source, minimizing data transfer and leveraging the source's optimized execution engine.
The folding algorithm is greedy and non-resumable: once a step breaks the fold, all subsequent steps execute locally regardless of their individual foldability. This makes step ordering a primary optimization lever—placing foldable operations (filters, column selections, sorts, groupings) before non-foldable operations (pivots, custom M functions, cross-source merges) maximizes the data reduction performed by the source engine. This concept is foundational to advanced features like incremental refresh, Dataflows, and DirectQuery composite models, all of which depend on efficient fold chains to function at scale.