Query Folding in Power Query: Complete Troubleshooting Guide

<p>Query folding is the single most impactful performance optimization in Power BI data refresh, yet it remains one of the most misunderstood concepts among Power BI developers. When query folding works correctly, Power Query translates your M language transformations into native SQL (or the source system's native query language) and pushes execution to the source database—where the engine is optimized for filtering, joining, aggregating, and sorting millions of rows. When query folding breaks, Power Query downloads the entire unfiltered dataset into memory and performs transformations locally using the mashup engine, which is orders of magnitude slower and consumes far more memory and capacity.</p>

<p>The difference is not marginal. A folded query against a 500-million-row SQL Server table that filters to 50,000 rows executes a single SQL statement that returns 50,000 rows in seconds. The same query without folding downloads all 500 million rows over the network, loads them into the mashup engine's memory, and then filters locally—a process that can take hours, consume gigabytes of RAM, and frequently timeout or crash the gateway. Our <a href="/services/power-bi-consulting">Power BI consulting practice</a> has seen enterprise refresh times drop from 4+ hours to under 10 minutes simply by restoring query folding that was inadvertently broken by a single Power Query step.</p>

<h2>What Query Folding Actually Does</h2>

<p>Query folding (also called predicate pushdown or query delegation) is the process by which the Power Query engine translates M language expressions into the native query language of the data source. For SQL-based sources, this means generating a SQL SELECT statement. For OData sources, it means generating URL query parameters. For Analysis Services, it means generating DAX or MDX queries.</p>

<p>The folding process works step by step through your Power Query transformation chain:</p>

<ol> <li><strong>Source connection</strong>: Power Query connects to the data source and identifies its query capabilities</li> <li><strong>Step evaluation</strong>: Each subsequent step (filter, column selection, sort, group, join) is evaluated for foldability—can this operation be expressed in the source's native query language?</li> <li><strong>Native query construction</strong>: All consecutive foldable steps are combined into a single native query</li> <li><strong>Fold barrier</strong>: When a step cannot be folded, it becomes the fold barrier. This step and all subsequent steps execute locally in the mashup engine</li> <li><strong>Execution</strong>: The native query executes on the source, returns the result set, and any remaining non-folded steps process the data locally</li> </ol>

<p>The critical insight: <strong>folding is cumulative and sequential</strong>. Once a step breaks folding, every step after it also executes locally—even if those subsequent steps are individually foldable operations. This means a single non-foldable step in the middle of your query can force the entire downstream transformation chain to execute locally.</p>

<h2>How to Verify Query Folding</h2>

<p>Power BI provides three methods to verify whether your Power Query steps are folding to the source.</p>

<h3>Method 1: View Native Query (Right-Click Step)</h3>

<p>In the Power Query Editor, right-click any step in the Applied Steps pane. If "View Native Query" is available and not grayed out, that step is folding. Click it to see the exact SQL (or other native query) that Power Query will send to the source. If "View Native Query" is grayed out, that step is not folding—and neither is any step after it.</p>

<p><strong>Important caveat</strong>: "View Native Query" being available does not guarantee complete folding. It confirms that the specific step you right-clicked is included in the folded query. Always check the <em>last</em> step in your query to confirm end-to-end folding.</p>

<h3>Method 2: Query Folding Indicators (Power BI Desktop)</h3>

<p>Power BI Desktop (since the March 2023 update) includes visual query folding indicators in the Applied Steps pane. Each step displays an icon:</p>

<ul> <li><strong>Green (folded)</strong>: The step is included in the native query sent to the source</li> <li><strong>Yellow (partial fold)</strong>: Some parts of the step fold, others do not (common with complex conditional logic)</li> <li><strong>Red (not folded)</strong>: The step executes locally in the mashup engine. This is the fold barrier if the preceding step was green or yellow</li> </ul>

<p>Enable this feature via File &gt; Options &gt; Power Query Editor &gt; Query Folding Indicators. This should be the first thing you enable in every Power BI Desktop installation.</p>

<h3>Method 3: Query Diagnostics</h3>

<p>For detailed analysis, use Tools &gt; Start Diagnostics in the Power Query Editor, run a refresh preview, then Stop Diagnostics. The diagnostics output includes a "Data Source Query" column that shows the exact native queries sent to each source. This method captures all queries, including those from multiple sources in a single Power Query, and provides timing information for each query.</p>

<h3>Method 4: SQL Server Profiler / Extended Events</h3>

<p>For SQL Server and Azure SQL sources, you can trace the actual queries arriving at the database using SQL Server Profiler (legacy) or Extended Events (recommended). This is the definitive verification—if you see a comprehensive SQL statement with WHERE clauses, GROUP BY, and JOINs matching your Power Query steps, folding is working. If you see a simple `SELECT * FROM table` with no predicates, folding has broken and the mashup engine is pulling the entire table.</p>

<h2>Operations That Fold vs. Operations That Break Folding</h2>

<p>Understanding which M language operations fold and which break folding is essential for writing performant Power Queries. The following classification applies to SQL-based sources (SQL Server, Azure SQL, PostgreSQL, Oracle, MySQL). Other sources have different folding capabilities.</p>

<h3>Operations That Typically Fold</h3>

<table> <thead><tr><th>Operation</th><th>M Function</th><th>SQL Equivalent</th></tr></thead> <tbody> <tr><td>Filter rows</td><td>Table.SelectRows</td><td>WHERE clause</td></tr> <tr><td>Select columns</td><td>Table.SelectColumns</td><td>SELECT column list</td></tr> <tr><td>Remove columns</td><td>Table.RemoveColumns</td><td>SELECT (excluding columns)</td></tr> <tr><td>Sort rows</td><td>Table.Sort</td><td>ORDER BY</td></tr> <tr><td>Group by / aggregate</td><td>Table.Group</td><td>GROUP BY with aggregates</td></tr> <tr><td>Remove duplicates</td><td>Table.Distinct</td><td>DISTINCT</td></tr> <tr><td>Top N rows</td><td>Table.FirstN</td><td>TOP N / LIMIT</td></tr> <tr><td>Rename columns</td><td>Table.RenameColumns</td><td>AS alias</td></tr> <tr><td>Change data type</td><td>Table.TransformColumnTypes</td><td>CAST / CONVERT</td></tr> <tr><td>Merge queries (join)</td><td>Table.NestedJoin</td><td>JOIN</td></tr> <tr><td>Append queries (union)</td><td>Table.Combine</td><td>UNION ALL</td></tr> <tr><td>Replace values (simple)</td><td>Table.ReplaceValue</td><td>CASE WHEN / REPLACE</td></tr> <tr><td>Conditional column (simple)</td><td>Table.AddColumn with if/then</td><td>CASE WHEN</td></tr> </tbody> </table>

<h3>Operations That Typically Break Folding</h3>

<table> <thead><tr><th>Operation</th><th>M Function</th><th>Why It Breaks</th></tr></thead> <tbody> <tr><td>Add column from examples</td><td>Table.AddColumn (complex patterns)</td><td>Generated M code often uses functions without SQL equivalents</td></tr> <tr><td>Pivot columns</td><td>Table.Pivot</td><td>Dynamic column creation cannot be expressed in static SQL</td></tr> <tr><td>Unpivot columns</td><td>Table.UnpivotOtherColumns</td><td>Some sources fold UNPIVOT, most do not</td></tr> <tr><td>Custom M functions</td><td>User-defined functions</td><td>No SQL equivalent for arbitrary M logic</td></tr> <tr><td>Text.Contains (partial match)</td><td>Table.SelectRows with Text.Contains</td><td>Some sources fold to LIKE, others do not</td></tr> <tr><td>List operations</td><td>List.Transform, List.Select</td><td>No SQL equivalent for M list processing</td></tr> <tr><td>Merge columns</td><td>Table.CombineColumns</td><td>Text concatenation may not fold depending on source</td></tr> <tr><td>Extract (text operations)</td><td>Text.Start, Text.Range</td><td>Some fold to SUBSTRING, many do not</td></tr> <tr><td>Buffer tables</td><td>Table.Buffer</td><td>Forces local materialization by design</td></tr> <tr><td>Value.NativeQuery (manual SQL)</td><td>Value.NativeQuery</td><td>Subsequent steps cannot fold on top of a manually specified query</td></tr> </tbody> </table>

<h2>Step-by-Step Troubleshooting Guide</h2>

<p>When you discover that query folding has broken (red indicators, grayed-out "View Native Query", or slow refresh times), follow this systematic troubleshooting process.</p>

<h3>Step 1: Identify the Fold Barrier</h3>

<p>Starting from the last step in your query, right-click each step moving upward until you find the first step where "View Native Query" becomes available. The step immediately after this is your fold barrier—the step that breaks folding.</p>

<h3>Step 2: Analyze the Breaking Step</h3>

<p>Examine the M code of the fold barrier step. Ask: what operation is this step performing, and does the source system have a native equivalent? Common scenarios:</p>

<ul> <li><strong>Complex conditional logic</strong>: Nested if/then/else with text operations. Simplify or move to a DAX calculated column instead.</li> <li><strong>Text manipulation</strong>: Text.Trim, Text.Clean, Text.PadStart. These often do not fold. Consider creating a SQL view on the source that performs the text manipulation, then query the view from Power Query.</li> <li><strong>Custom column with M functions</strong>: Functions like DateTime.LocalNow(), Duration.TotalDays, or Number.Round may not have foldable equivalents. Move calculation to source SQL or post-load DAX.</li> <li><strong>Reordering steps</strong>: Sometimes the fold barrier exists because steps are in the wrong order. A foldable filter placed after a non-foldable step cannot fold. Move the filter before the non-foldable step.</li> </ul>

<h3>Step 3: Apply the Fix Pattern</h3>

<p>For each fold barrier scenario, apply the appropriate fix:</p>

<p><strong>Pattern A: Push to source</strong> — Create a SQL view or stored procedure on the source database that performs the non-foldable transformation. Point Power Query at the view instead of the base table. This is the most reliable fix and moves computation to the optimized database engine.</p>

<p><strong>Pattern B: Reorder steps</strong> — If you have foldable steps (filters, column selection) after the fold barrier, move them before it. Power Query does not automatically reorder steps for optimal folding. You must do this manually. Check that reordering does not change the query's logical result.</p>

<p><strong>Pattern C: Replace M function with foldable equivalent</strong> — Some M operations have alternative syntax that folds when the original does not. For example, <code>Table.SelectRows(source, each [Column] &lt;&gt; null)</code> folds, but <code>Table.SelectRows(source, each [Column] &lt;&gt; null and [Column] &lt;&gt; "")</code> may not fold on certain sources. Test alternatives.</p>

<p><strong>Pattern D: Accept partial folding</strong> — If the non-foldable step is the last step and operates on a small result set (because earlier foldable steps already filtered millions of rows down to thousands), the performance impact is minimal. Document the intentional fold break and move on.</p>

<p><strong>Pattern E: Move to DAX</strong> — If the transformation is a calculation for reporting purposes (conditional categorization, running totals, percentage of total), consider removing it from Power Query entirely and implementing it as a DAX calculated column or measure. This eliminates the fold barrier and often performs better because DAX operates on the compressed in-memory model.</p>

<h3>Step 4: Verify the Fix</h3>

<p>After applying a fix, right-click the last step and confirm "View Native Query" is available. Examine the generated SQL to verify it includes the expected WHERE, JOIN, GROUP BY clauses. Run a refresh preview and check Query Diagnostics to confirm the expected native query is executed. Compare refresh times before and after.</p>

<h2>Native Query Deep Dive</h2>

<p>When Power Query generates a native query, understanding its structure helps you verify correctness and optimize further.</p>

<h3>Reading the Generated SQL</h3>

<p>Power Query generates subquery-heavy SQL that wraps each step as a nested SELECT. For example, a query that connects to a table, filters by date, selects specific columns, and groups by category might generate:</p>

<pre><code>SELECT [category], SUM([amount]) as [total_amount] FROM ( SELECT [category], [amount] FROM ( SELECT * FROM [dbo].[transactions] WHERE [transaction_date] &gt;= '2026-01-01' ) AS [subquery1] ) AS [subquery2] GROUP BY [category] </code></pre>

<p>This nested structure looks inefficient but is semantically equivalent to a flat query. SQL Server's query optimizer will flatten the subqueries into an efficient execution plan. Do not worry about the nesting—focus on confirming that the correct predicates and aggregations are present.</p>

<h3>Using Value.NativeQuery for Manual SQL</h3>

<p>When Power Query's generated SQL is not optimal, or when you need specific query hints, CTEs, or window functions, you can write your own SQL using <code>Value.NativeQuery</code>:</p>

<pre><code>let Source = Sql.Database("server.database.windows.net", "mydb"), Query = Value.NativeQuery(Source, " SELECT category, SUM(amount) as total_amount, COUNT(*) as transaction_count, AVG(amount) as avg_amount FROM dbo.transactions WHERE transaction_date &gt;= @startDate GROUP BY category HAVING COUNT(*) &gt; 100 ", [startDate = #date(2026, 1, 1)]) in Query </code></pre>

<p><strong>Critical warning</strong>: Steps added after a Value.NativeQuery will NOT fold. The manual SQL becomes a fixed subquery, and Power Query cannot modify it to incorporate additional steps. If you use Value.NativeQuery, ensure it includes all necessary filtering, aggregation, and transformation. Any steps added afterward will execute locally. For complex scenarios requiring both manual SQL and post-processing, consider splitting into two queries: one with the native SQL that feeds a staging table, and a second query that references the staging table (which may fold its own steps).</p>

<h2>M Language Patterns: Foldable vs. Non-Foldable</h2>

<p>The specific M language syntax you use determines whether an operation folds. Here are patterns with their folding behavior for SQL-based sources.</p>

<h3>Date Filtering Patterns</h3>

<p><strong>Folds</strong>:</p> <pre><code>// Absolute date comparison Table.SelectRows(source, each [OrderDate] &gt;= #date(2026, 1, 1))

// Column-to-column comparison Table.SelectRows(source, each [ShipDate] &gt; [OrderDate]) </code></pre>

<p><strong>May not fold</strong>:</p> <pre><code>// Relative date using DateTime.LocalNow() Table.SelectRows(source, each [OrderDate] &gt;= Date.AddMonths(DateTime.LocalNow(), -3))

// Date function composition Table.SelectRows(source, each Date.Year([OrderDate]) = 2026) </code></pre>

<p><strong>Fix for relative dates</strong>: Calculate the date boundary as a parameter before the query:</p> <pre><code>let ThreeMonthsAgo = Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -3), Source = Sql.Database("server", "db"), Filtered = Table.SelectRows(Source, each [OrderDate] &gt;= ThreeMonthsAgo) in Filtered </code></pre>

<p>By computing <code>ThreeMonthsAgo</code> as a scalar value before passing it into the filter, Power Query can fold the comparison as a parameterized WHERE clause.</p>

<h3>Text Filtering Patterns</h3>

<p><strong>Folds (on most SQL sources)</strong>:</p> <pre><code>// Exact match Table.SelectRows(source, each [Status] = "Active")

// Starts with Table.SelectRows(source, each Text.StartsWith([Name], "EPC")) </code></pre>

<p><strong>May not fold</strong>:</p> <pre><code>// Contains (depends on source) Table.SelectRows(source, each Text.Contains([Description], "power bi"))

// Complex text operations Table.SelectRows(source, each Text.Length(Text.Trim([Name])) &gt; 0) </code></pre>

<h3>Null Handling Patterns</h3>

<p><strong>Folds</strong>:</p> <pre><code>// IS NULL / IS NOT NULL Table.SelectRows(source, each [Email] &lt;&gt; null) Table.SelectRows(source, each [Email] = null) </code></pre>

<p><strong>May not fold</strong>:</p> <pre><code>// Coalesce with complex fallback Table.ReplaceValue(source, null, each [AlternateEmail], Replacer.ReplaceValue, {"Email"}) </code></pre>

<h2>Microsoft Fabric and Direct Lake Implications</h2>

<p>Query folding takes on a different dimension in Microsoft Fabric environments, particularly with Direct Lake mode.</p>

<h3>Direct Lake: Beyond Traditional Folding</h3>

<p><a href="/blog/power-bi-direct-lake-mode-guide-2026">Direct Lake mode</a> bypasses the import/refresh paradigm entirely. Instead of Power Query refreshing data from a source into an in-memory model, Direct Lake reads Delta Parquet files directly from OneLake at query time. There is no Power Query refresh step, so traditional query folding does not apply to the query-time read path.</p>

<p>However, query folding is critically important in the <strong>data preparation pipeline</strong> that populates the Lakehouse tables that Direct Lake reads. If you use Dataflows Gen2 to load data into Lakehouse tables, the folding behavior of those Dataflows determines how efficiently data moves from the source into OneLake. A non-folding Dataflow Gen2 will still download entire tables into the mashup engine before writing to the Lakehouse—negating the performance benefits of the Fabric architecture.</p>

<h3>Fabric Lakehouse SQL Endpoint Folding</h3>

<p>When Power Query connects to a Fabric Lakehouse via its SQL analytics endpoint, query folding works similarly to SQL Server—the endpoint supports standard T-SQL, and Power Query can fold most operations. This is the recommended pattern when you need Power Query transformations on Lakehouse data (as opposed to using Spark notebooks).</p>

<h3>Dataflows Gen2 Folding Optimization</h3>

<p>In Dataflows Gen2, query folding is especially important because Dataflows execute on shared Fabric capacity. Non-folding Dataflows consume mashup engine compute, which counts against your Fabric capacity units (CUs). Folding Dataflows push computation to the source and minimize CU consumption. For capacity planning guidance, see our <a href="/blog/microsoft-fabric-capacity-planning-sizing-guide-2026">Fabric capacity planning guide</a>.</p>

<p>Best practices for Dataflows Gen2 folding:</p> <ul> <li>Always check folding indicators before publishing a Dataflow</li> <li>Use staging Lakehouses for intermediate results when multi-step transformations break folding—load the raw data folded, then apply non-foldable transformations in a second Dataflow or notebook</li> <li>Prefer Spark notebooks over Dataflows for complex transformations on large datasets—notebooks execute on Spark compute, not the mashup engine, and scale horizontally</li> <li>Monitor Dataflow refresh duration in the <a href="/blog/fabric-capacity-metrics">Fabric Capacity Metrics app</a> to identify non-folding bottlenecks</li> </ul>

<h2>Advanced Troubleshooting Scenarios</h2>

<h3>Scenario 1: Merge Queries Breaking Folding</h3>

<p>Table.NestedJoin (Merge Queries) folds when both tables are on the same SQL server and database. It breaks when:</p> <ul> <li>Tables are on different servers or databases (cross-server joins cannot fold)</li> <li>One table is SQL and the other is a local Excel file or CSV</li> <li>The join key data types do not match (Power Query performs local type coercion)</li> <li>You expand all columns from the nested table (expanding a large number of columns may not fold)</li> </ul> <p><strong>Fix</strong>: Ensure both tables are on the same source. If cross-source joins are required, load both tables to a staging database or Fabric Lakehouse first, then join within the single source.</p>

<h3>Scenario 2: Incremental Refresh and Folding</h3>

<p><a href="/blog/power-bi-incremental-refresh-data-partitioning-guide-2026">Incremental refresh</a> requires query folding on the RangeStart and RangeEnd date parameters. If the date filter does not fold, incremental refresh cannot partition the data, and every refresh pulls the entire dataset. Verify folding specifically on the date filter step, not just the overall query. Common reasons incremental refresh folding breaks:</p> <ul> <li>The date column is stored as text in the source (CAST is needed, which may not fold)</li> <li>The date filter uses Date.From() or other conversion functions</li> <li>Steps between the source and the date filter break folding</li> </ul>

<h3>Scenario 3: Parameters and Dynamic Sources</h3>

<p>Power Query parameters used in source connection strings (server name, database name) enable dynamic source configuration but can affect folding. Ensure parameters used in filter predicates are scalar values (text, number, date), not tables or lists. Scalar parameters fold as parameterized query values. Table or list parameters force local evaluation.</p>

<h3>Scenario 4: Gateway Performance</h3>

<p>For on-premises data sources accessed through the <a href="/blog/power-bi-gateway-setup">on-premises data gateway</a>, query folding is even more critical. Non-folding queries transfer the full dataset from the on-premises source, through the gateway, over the network to the Power BI service. This saturates gateway bandwidth and can block other gateway-dependent datasets. Always verify folding for gateway-connected sources and consider creating SQL views on-premises to guarantee folding.</p>

<h2>Performance Benchmarking</h2>

<p>To quantify the impact of query folding on your specific datasets, run this benchmarking process:</p>

<ol> <li><strong>Baseline</strong>: Record the current refresh time, data source query count (from Query Diagnostics), and gateway data transfer volume</li> <li><strong>Identify fold barriers</strong>: Use the troubleshooting process above to identify all fold barriers</li> <li><strong>Fix one barrier at a time</strong>: Apply fixes sequentially, measuring refresh time after each fix to attribute improvement to specific changes</li> <li><strong>Document results</strong>: Record before/after metrics for each fix. This data justifies the effort to stakeholders and provides a reference for future optimization</li> </ol>

<p>Typical results from our <a href="/services/power-bi-consulting">Power BI consulting engagements</a>:</p>

<table> <thead><tr><th>Scenario</th><th>Before (No Folding)</th><th>After (Folding Restored)</th><th>Improvement</th></tr></thead> <tbody> <tr><td>50M row fact table, date filter</td><td>45 min refresh</td><td>3 min refresh</td><td>93% faster</td></tr> <tr><td>10M row table, 5 column selection</td><td>12 min refresh</td><td>2 min refresh</td><td>83% faster</td></tr> <tr><td>Cross-database join (same server)</td><td>90 min (local join)</td><td>8 min (SQL join)</td><td>91% faster</td></tr> <tr><td>Incremental refresh partition</td><td>Full refresh 2 hours</td><td>Incremental 5 min</td><td>96% faster</td></tr> </tbody> </table>

<h2>Checklist: Query Folding Best Practices</h2>

<ul> <li>Enable query folding indicators in Power BI Desktop Options immediately</li> <li>Check folding on the <strong>last step</strong> of every Power Query before publishing</li> <li>Place foldable steps (filters, column selection) as early as possible in the query</li> <li>Move non-foldable transformations to source views, DAX, or Spark notebooks</li> <li>Use Value.NativeQuery only as a last resort, and never add steps after it</li> <li>Pre-compute scalar parameters (dates, thresholds) before using them in filters</li> <li>Verify incremental refresh date filters fold independently</li> <li>Monitor Dataflow Gen2 refresh durations for folding regression</li> <li>Create SQL views for complex transformations that cannot fold from M language</li> <li>Document intentional fold breaks with comments in the M code explaining the performance tradeoff</li> <li>For gateway-connected sources, treat folding as mandatory—not optional</li> </ul>

<p>Query folding is not an advanced topic—it is a fundamental requirement for any Power BI deployment processing more than trivial data volumes. Every Power Query you build should be evaluated for folding as part of code review, and every refresh performance issue should begin with a folding investigation. <a href="/contact">Contact EPC Group</a> for a Power Query performance audit that identifies folding gaps, implements fixes, and establishes query development standards that prevent folding regressions in your organization.</p>