Mastering Power BI Query Folding: Diagnosing Fold Boundaries, Optimizing Power Query Steps, and Reducing Data Source Load for Scalable Model Refreshes

Introduction

Picture this: your Power BI model refreshes fine in development — maybe 3 or 4 minutes against a SQL Server database holding 10 million rows of sales transactions. You promote it to production, add a few more transformation steps in Power Query to handle some edge cases your QA team caught, and suddenly the refresh balloons to 45 minutes. The database server's CPU spikes. The gateway logs show a massive data transfer. Your infrastructure team is sending you emails.

What changed? You almost certainly broke query folding somewhere in your transformation chain — probably without realizing it. A single unsupported step silently disabled the ability of Power Query to push your filtering, joining, and aggregation logic back to the database engine, forcing the Power BI engine to download millions of raw rows and process them locally. That's the kind of invisible performance cliff that bites even experienced Power BI developers, because Power Query will happily execute your steps either way. It just won't tell you when it switched from efficient to catastrophically slow.

By the end of this lesson, you'll have a genuinely deep understanding of what query folding is, how to diagnose exactly where folding breaks down in your queries, how to restructure your Power Query steps to maximize folding, and how to architect multi-table models with folding efficiency in mind. You'll also understand the cases where folding cannot happen — and the design patterns that let you work around those constraints without sacrificing transformation capability.

What you'll learn:

• What query folding is, why it matters, and how it works under the hood
• How to use the View Native Query option and step-level query folding indicators to precisely locate fold boundaries
• Which Power Query transformations break folding and why — including non-obvious cases
• Techniques for restructuring M code to extend the folding zone as far down your transformation chain as possible
• How incremental refresh depends on query folding, and what happens when they interact
• Architecture-level patterns for organizing queries in multi-source models to maximize server-side execution

---

Prerequisites

This lesson assumes you're comfortable with Power Query's M language syntax and can read and write basic M expressions. You should understand the core concept of how Power BI loads data — if you haven't already, review Mastering Power Query in Power BI: Transforming, Cleaning, and Shaping Data Before It Hits Your Model and Understanding Power Query Data Types and Column Profiling: Preventing Errors Before They Reach Your Report first. You should also have a working understanding of storage modes, since folding behavior differs significantly across Import, DirectQuery, and Live Connection — see Understanding Power BI Storage Modes: Import, DirectQuery, and Live Connection Compared if you need a refresher.

---

What Query Folding Actually Is (and Why It's Not Just an Optimization)

Query folding is the mechanism by which Power Query translates your M transformation steps into a query that executes natively on the data source — typically SQL for relational databases, but also OData filters, SAP BAPI calls, or other source-native query languages. When folding works, the data source receives a single optimized query that applies your filters, joins, groupings, and column selections. When folding doesn't work, Power Query downloads a full (or much larger) dataset to the local Power Query engine and processes your transformations there.

This distinction sounds simple, but the performance implications are enormous. Consider a table with 50 million rows of event log data. You want only the last 90 days, only rows where the EventType column equals "Error," and only four of the twenty available columns. With folding, the database executes something like:

SELECT
    EventId,
    EventTimestamp,
    EventType,
    MachineName
FROM dbo.EventLog
WHERE EventType = 'Error'
  AND EventTimestamp >= DATEADD(day, -90, GETDATE())

The database returns maybe 200,000 rows. Without folding, Power Query issues SELECT * FROM dbo.EventLog, pulls all 50 million rows across the network, then applies your filter and column selection locally. The difference in refresh time and infrastructure load is not 10% — it can be multiple orders of magnitude.

Key insight: Query folding isn't really an "optimization" in the conventional sense — it's the expected behavior for queries against structured data sources. Treating it as optional is like treating indexes as optional. Technically true, practically devastating at scale.

Folding operates through a concept called a query plan. As you add steps in Power Query, the M engine maintains an internal representation of the transformations you've applied. For foldable sources, it attempts to express that representation as a native query. Each step either extends the native query (folds) or forces a context switch to local processing (breaks folding). Once folding breaks, it stays broken for all subsequent steps in that query — there is no mechanism to "re-enable" folding after it has been broken.

This propagation behavior is critical: it means a single poorly placed step in a 20-step query can make 18 of those steps execute locally. The fold boundary is the step where this transition happens, and it's not always obvious.

---

Diagnosing Fold Boundaries: The Tools You Have

View Native Query

The primary diagnostic tool for query folding is the View Native Query option in Power Query Editor. Right-click any step in the Applied Steps pane and look for "View Native Query." If it's clickable, that step folds to the data source, and you can see the exact SQL (or OData query, or whatever the source uses) that Power Query would send. If it's greyed out, that step does not fold.

This greyed-out vs. clickable behavior is your first diagnostic signal. Work through your Applied Steps from the top down. The first step where "View Native Query" becomes greyed out is your fold boundary — every step from that point on executes locally.

Warning: "View Native Query" being available doesn't mean your query is maximally efficient. Power Query might fold the step but generate poorly-performing SQL with unnecessary subqueries, redundant predicates, or non-SARGable expressions. Always look at the actual SQL that gets generated for complex queries.

Query Diagnostics

Power Query's built-in Query Diagnostics feature (under the Tools ribbon in Power Query Editor) captures detailed telemetry about every query that runs during a refresh or preview. To use it:

1. In Power Query Editor, go to the Tools tab
2. Click "Start Diagnostics"
3. Click "Refresh Preview" or perform whatever action you want to analyze
4. Click "Stop Diagnostics"
5. Two diagnostic tables appear in your query list: a summary and a detailed log

The detailed diagnostics table shows you the data source query that was actually sent (in the Data Source Query column) and the row count transferred. If you see a data source query that's essentially SELECT * FROM [your_table], folding has broken at or before the first step that should have filtered data.

Table.View and Value.NativeQuery

For programmatic fold checking in M code, you can use Value.NativeQuery to explicitly push a raw SQL query to the source — but this is an escape hatch, not a diagnostic tool. More useful diagnostically is understanding that when you call Table.View, you're essentially defining a foldable virtual table. If Power Query can express your transformation as a call to the source's native query API, it will. If not, it falls back to local processing.

There's also a lesser-known approach: wrapping a step in try ... otherwise and inspecting whether the native query is accessible. Some experienced M developers build diagnostic functions that programmatically test foldability. For most scenarios, the right-click inspection approach is sufficient.

Tip: Keep a separate copy of your query with no steps past the source step, and build up steps one at a time, checking fold status after each addition. This is tedious but it's the fastest way to find the exact step that breaks folding in a complex transformation chain.

SQL Profiler and Extended Events

For SQL Server sources, the most accurate way to see what Power Query is actually sending is to run a SQL Server Profiler trace or Extended Events session while triggering a refresh. This shows you the exact T-SQL statements that arrive at the server, including execution plans if you capture those events. This is especially valuable when you suspect Power Query is folding but generating inefficient SQL — something that's hard to see from the Power Query Editor alone.

---

Which Transformations Fold and Which Don't

Understanding which Power Query operations support folding is half the battle. The full answer is connector-specific — SQL Server supports a much richer set of foldable operations than, say, an Excel file or a folder connector — but we can establish reliable patterns.

Operations That Generally Fold (SQL Server and Similar)

• Filter Rows (Table.SelectRows) — as long as the predicate uses column references and literal values or parameters
• Remove Columns / Select Columns (Table.RemoveColumns, Table.SelectColumns) — translates to explicit column lists in SELECT
• Sort (Table.Sort) — translates to ORDER BY
• Keep Top Rows (Table.FirstN) — translates to TOP or LIMIT
• Group By (Table.Group) with standard aggregations like Sum, Count, Average, Min, Max
• Merge Queries (Table.NestedJoin) when both source tables are foldable and from the same connection
• Append Queries (Table.Combine) when sources are from the same connection
• Rename Columns (Table.RenameColumns) — simple alias, translates to AS in SELECT
• Change Type (Table.TransformColumnTypes) — data type casting
• Add Column with simple expression (Table.AddColumn) — if the expression is a simple arithmetic or comparison operation on existing columns

Operations That Generally Break Folding

• Add Column using custom M functions — any custom function that references M-only logic
• Invoke Custom Function — always breaks folding unless you've explicitly implemented the function using Table.View
• Expand Record Column or List Column — when the column was created from a local transformation, not a foldable join
• Pivot / Unpivot Columns (Table.Pivot, Table.Unpivot) — these are more nuanced; Table.Unpivot sometimes folds depending on the connector
• Add Column from examples — generates M expressions that often use non-foldable functions
• Using Table.Buffer — explicitly prevents folding; buffered tables are materialized in memory
• Accessing a parameter that's not a simple value — if your parameter references a function or another query, folding may break
• Merging tables from different data sources — cross-source merges never fold (they can't; there's no single query engine that spans both sources)

Note: The foldability of some operations is version-dependent. Power Query's M engine has improved in how many operations it can fold across multiple release cycles. What was an infallible fold-breaker in 2019 might partially fold in a 2024 release. Always test with your specific connector version and dataset.

The Sneaky Non-Obvious Cases

Several fold-breaking situations catch even experienced developers off guard.

DateTime functions: Using DateTime.LocalNow() or DateTime.Date(DateTime.LocalNow()) in a filter step often breaks folding on some connectors because the engine can't guarantee the value is a fixed literal. Using a Power Query parameter with a date value works better. If you're filtering to a date range dynamically, define the date as a text parameter and convert it once, rather than calling DateTime.LocalNow() inline.

Conditional columns with complex logic: A Table.AddColumn that uses if [Status] = "Active" then 1 else 0 often folds because it's a simple CASE expression. But if Text.Contains([Description], "urgent") then 1 else 0 may fold differently depending on whether the connector can express LIKE or CONTAINS patterns.

Text transformations: Text.Upper([ColumnName]) sometimes folds (translating to UPPER() in SQL), but Text.Split([ColumnName], ",") never folds because it produces a list — a concept that doesn't map to relational SQL rows.

List.Contains in filters: Using List.Contains({"Value1", "Value2"}, [Column]) to filter rows usually folds into an IN clause in SQL. But if the list is dynamically constructed from another query or table, folding often breaks.

Table.Buffer: This one is often used intentionally to prevent folding (for example, to materialize a lookup table before a merge), but it's sometimes added accidentally by generated M code or copy-pasted patterns. Any Table.Buffer() call materializes the table in memory, permanently ending folding for that branch.

---

Restructuring M Code to Maximize the Folding Zone

The core strategy is to keep all foldable operations together at the top of your Applied Steps chain, before any non-foldable operations. Then batch your local transformations after the fold boundary. This sounds obvious, but it requires active management because Power Query's GUI and automatic step generation don't enforce or respect this ordering.

Reorder Steps Manually

If you use the GUI to add a filter after an Add Column step that broke folding, the filter executes locally. If you reorder so the filter runs before the Add Column, it might fold. The Applied Steps pane lets you drag steps to reorder them, but be careful — reordering can change the semantic meaning of your query if later steps depend on earlier ones.

In M code, reordering steps looks like this. Here's a problematic ordering:

let
    Source = Sql.Database("server", "database"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    // Step 1: Add a computed column — BREAKS FOLDING
    AddedRevenue = Table.AddColumn(
        Orders,
        "Revenue",
        each [Quantity] * [UnitPrice] * (1 - [DiscountRate]),
        type number
    ),
    // Step 2: Filter by date — NOW RUNS LOCALLY (bad!)
    FilteredByDate = Table.SelectRows(
        AddedRevenue,
        each [OrderDate] >= #date(2023, 1, 1)
    ),
    // Step 3: Filter by status — ALSO RUNS LOCALLY (bad!)
    FilteredByStatus = Table.SelectRows(
        FilteredByDate,
        each [Status] = "Shipped"
    )
in
    FilteredByStatus

Every row from the Orders table gets pulled into memory before any filtering happens. Reorder those filters:

let
    Source = Sql.Database("server", "database"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    // Step 1: Filter by date — FOLDS
    FilteredByDate = Table.SelectRows(
        Orders,
        each [OrderDate] >= #date(2023, 1, 1)
    ),
    // Step 2: Filter by status — FOLDS
    FilteredByStatus = Table.SelectRows(
        FilteredByDate,
        each [Status] = "Shipped"
    ),
    // Step 3: Add computed column — now only over filtered rows (local, but small)
    AddedRevenue = Table.AddColumn(
        FilteredByStatus,
        "Revenue",
        each [Quantity] * [UnitPrice] * (1 - [DiscountRate]),
        type number
    )
in
    AddedRevenue

Now the database executes the WHERE clause and returns a much smaller dataset. The computed column still runs locally, but over far fewer rows.

Key insight: The single most impactful refactoring you can do to query folding performance is to move all filter steps above any step that breaks folding. Reduce the row count at the source before doing anything locally.

Column Selection Early and Often

After your source step, immediately remove columns you don't need. A Table.SelectColumns or Table.RemoveColumns step that comes before any fold-breaking step translates into an explicit column list in the SELECT clause, reducing the data transferred even if subsequent steps run locally.

let
    Source = Sql.Database("server", "database"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    // Immediately select only the columns you need
    SelectedColumns = Table.SelectColumns(
        Orders,
        {"OrderId", "OrderDate", "CustomerId", "Status",
         "Quantity", "UnitPrice", "DiscountRate"}
    ),
    FilteredByDate = Table.SelectRows(
        SelectedColumns,
        each [OrderDate] >= #date(2023, 1, 1)
    )
    // ... rest of your steps
in
    FilteredByDate

Even if your source table has 40 columns with large text blobs or XML columns, this pattern ensures you're not pulling that data across the network.

Using Parameters for Dynamic Filter Values

If you need to filter based on a dynamically computed value — say, "the last complete month" — don't compute that value inline in your filter expression. Compute it once in a separate query (or use a Power Query parameter) and reference it.

// In a separate query called "FilterStartDate"
let
    Today = Date.From(DateTime.LocalNow()),
    FirstOfThisMonth = Date.StartOfMonth(Today),
    FirstOfLastMonth = Date.AddMonths(FirstOfThisMonth, -1)
in
    FirstOfLastMonth

// In your main Orders query
let
    Source = Sql.Database("server", "database"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    StartDate = FilterStartDate,  // reference the computed parameter
    FilteredOrders = Table.SelectRows(
        Orders,
        each [OrderDate] >= StartDate
    )
in
    FilteredOrders

When FilterStartDate resolves to a simple date value, Power Query can often include it as a literal in the folded SQL. This is more reliable than calling DateTime.LocalNow() inline.

When to Use Table.Buffer Intentionally

There's a legitimate use case for Table.Buffer: when you're merging a small reference/lookup table with a large fact table, and the lookup table comes from a non-foldable source (like an Excel file or a web API), buffering the lookup table prevents Power Query from re-fetching it for every row of the fact table.

// Buffer the small lookup table from Excel (already non-foldable)
let
    ExcelSource = Excel.Workbook(File.Contents("C:\Data\ProductCategories.xlsx")),
    CategorySheet = ExcelSource{[Name="Categories"]}[Data],
    BufferedCategories = Table.Buffer(CategorySheet)
in
    BufferedCategories

In this case, buffering is the right call. The damage (breaking folding on the Excel source) was already done by nature of using Excel as a source. Buffering prevents repeated network calls to re-read the file during the merge.

---

The Relationship Between Query Folding and Incremental Refresh

If you use incremental refresh — and at scale you almost certainly should — query folding is not optional. It's a hard requirement. When Scheduled Refresh and Incremental Refresh Strategies is set up, Power BI uses the RangeStart and RangeEnd parameters to instruct Power Query to retrieve only the data window for the refresh partition. If those parameters can't be pushed down to the data source via query folding, Power BI has no way to enforce incremental refresh partitioning efficiently. In the worst case, Power BI will pull the entire table every refresh cycle, completely defeating the purpose of incremental refresh.

The Power BI service will actually warn you during incremental refresh configuration if it detects that folding may not be occurring — but this is a heuristic, not a guarantee. The warning says something like "We couldn't detect that query folding is enabled." You should treat this as a hard error, not a soft suggestion.

Warning: A broken fold with incremental refresh enabled can result in your entire fact table being pulled on every scheduled refresh, turning a 5-minute incremental refresh into a 90-minute full refresh. Monitor your gateway logs and database query logs after enabling incremental refresh to confirm folding is actually working.

To verify that incremental refresh folding is working, set the RangeStart and RangeEnd parameters to specific values (not the datetime values Power BI will inject, but representative values you choose), trigger a refresh in the Power Query Editor, and use SQL Profiler to confirm that a WHERE OrderDate BETWEEN @RangeStart AND @RangeEnd clause (or equivalent) appears in the database-side query.

---

Cross-Source Merges and the Folding Boundary

When you merge two tables that come from different data sources, folding stops immediately at that merge step. Power Query cannot push a join between a SQL Server table and an Excel file to either engine — neither engine knows about the other. The merge happens locally in Power Query.

This has serious performance implications for large-scale models. If you're merging a 50-million-row fact table with a product dimension from an Excel file, Power Query will:

1. Pull all 50 million rows from SQL Server (folding works up to this step, but after the merge, those rows are in memory)
2. Pull all rows from Excel
3. Join them locally

The fix is to move your lookup data into the same data source as your fact table. If you can load the Excel data into a SQL Server table (even a staging table that your ETL process populates), the merge happens in SQL and folding is preserved.

In cases where you truly can't co-locate the data, consider using Mastering Power BI Dataflows: Building Reusable ETL Pipelines in Power BI Service to pre-join the tables in a dataflow, then connect Power BI to the dataflow output as a single source.

Tip: If you have a small lookup table (under a few thousand rows) that you need to merge with a large foldable source, another option is to expand the lookup into the M query itself as an inline table, then use that as a merge target. Power Query can sometimes convert the inline table to an IN clause or a series of CASE WHEN expressions in the folded SQL.

---

DirectQuery Mode and Query Folding

In DirectQuery mode, query folding isn't an optimization — it's a survival requirement. Every visual interaction in your report generates a DAX query that Power BI translates to a data source query. If the underlying Power Query transformations don't fold, that translation fails or produces disastrously slow queries.

For Mastering Power BI Composite Models: Combining DirectQuery and Import Mode for Real-Time and Historical Data Analysis, the folding architecture becomes even more complex. Import tables fold during the refresh process (and breaking folding there increases refresh time). DirectQuery tables must fold at query time (and breaking folding there makes every report interaction slow). The Power BI engine tries to push as much computation as possible to the DirectQuery source, using the DAX-to-SQL translation layer — but your Power Query transformations set the scope of what's available at the source level.

In DirectQuery mode, keep your Power Query transformations minimal. Complex transformations in Power Query that might break folding should be implemented in the database layer instead (as views, stored procedures, or materialized tables). Think of the DirectQuery Power Query layer as a thin adapter that selects, renames, and lightly filters — not a transformation engine.

---

Optimizing for Performance at the Architecture Level

Individual query optimization matters, but architecture-level decisions often have a bigger impact.

Pre-Aggregate at the Source

If your reporting needs are known, aggregate in the database before Power BI even sees the data. Instead of pulling a transaction table with 100 million rows and doing Table.Group in Power Query, create a SQL view or stored procedure that returns pre-aggregated daily or weekly totals. The SQL engine will aggregate with indexes and parallel execution plans. Power Query's M engine, running on your gateway machine, does not have those advantages.

This connects naturally to the aggregations strategy covered in Mastering Power BI Aggregations: Building Pre-Aggregated Tables to Accelerate Large-Scale DirectQuery and Import Models — you can use Power BI aggregation tables alongside query folding to create a layered performance strategy.

Use Views and Stored Procedures at the Source

Connecting Power BI to a SQL view rather than a raw table has several advantages for folding. The view definition lives at the database, where it can be optimized by a DBA, and Power Query folds additional steps on top of the view just as it would against a table. Stored procedures, however, don't fold — Power Query can call them but can't push additional predicates into them, because it can't modify the stored procedure's logic.

Validate Folding in Your Development Process

Build fold validation into your query development workflow. For each query that connects to a structured source, the default assumption should be "this must fold." When a step breaks folding, make an explicit decision: can I restructure to preserve folding, or is this transformation genuinely local-only? Document that decision in a comment in the M code.

// NOTE: Text.Split in AddedDomainParts breaks folding intentionally.
// All filtering (date range, active status, region) happens above this step.
// As of this query version, ~180K rows pass the fold boundary — acceptable.
let
    Source = Sql.Database("server", "CRM"),
    Contacts = Source{[Schema="dbo", Item="Contacts"]}[Data],
    FilteredActive = Table.SelectRows(Contacts, each [IsActive] = true),
    FilteredByRegion = Table.SelectRows(FilteredActive, each [Region] = "NA"),
    FilteredByDate = Table.SelectRows(
        FilteredByRegion,
        each [CreatedDate] >= #date(2022, 1, 1)
    ),
    SelectedCols = Table.SelectColumns(
        FilteredByDate,
        {"ContactId", "Email", "Region", "CreatedDate"}
    ),
    // FOLD BOUNDARY: Text.Split does not fold
    AddedDomainParts = Table.AddColumn(
        SelectedCols,
        "EmailDomain",
        each Text.Split([Email], "@"){1},
        type text
    )
in
    AddedDomainParts

This kind of documentation makes your queries maintainable and helps the next person understand why certain steps are ordered the way they are.

---

Hands-On Exercise

This exercise uses a SQL Server database (the AdventureWorks sample database works well, as does any database with at least one large transactional table).

Scenario: You're building a Power BI model for a sales analytics report. The fact table is Sales.SalesOrderDetail with joined header data from Sales.SalesOrderHeader. You need to filter to orders placed in 2022 and 2023, calculate a revenue column, and join with a product category lookup you have in an Excel file.

Step 1: Connect to SQL Server and examine initial fold status

Open Power Query Editor, connect to your SQL Server database, and navigate to Sales.SalesOrderDetail. Right-click the Source step and check "View Native Query." You should see a simple SELECT statement — this folds.

Step 2: Build the query in the wrong order (deliberately)

Add these steps in this order:

1. Add Column: Revenue = [OrderQty] * [UnitPrice] * (1 - [UnitPriceDiscount])
2. Filter Rows: OrderDate >= 1/1/2022 (you'll need to merge with the header table first to get OrderDate)
3. Remove unnecessary columns

After each step, right-click and check "View Native Query." Note where it becomes greyed out.

Step 3: Rewrite with correct fold-preserving order

Restructure the M query to:

1. Merge with SalesOrderHeader (same SQL source — this should fold)
2. Expand only the OrderDate and OrderStatus columns from the header
3. Filter by date range (folds)
4. Filter by status (folds)
5. Select only needed columns (folds)
6. Add the Revenue computed column (breaks folding here, but over a much smaller row set)

Open the Advanced Editor and write this version directly in M, then verify the fold boundary using "View Native Query" at each step.

Step 4: Add the Excel lookup (cross-source merge)

Add the product category Excel file as a separate query. Merge it with your main query. Observe that:

• Before the merge: View Native Query works on the SQL steps
• After the merge: View Native Query is greyed out

Step 5: Quantify the difference

Use Query Diagnostics (Tools > Start Diagnostics > Refresh Preview > Stop Diagnostics) to compare the data transferred in the fold-broken version vs. your optimized version. Record the row counts and data transfer sizes. In a real production scenario with millions of rows, this difference translates directly to refresh time.

Bonus: Set up RangeStart and RangeEnd parameters on your optimized query and confirm that they appear in the "View Native Query" output, demonstrating readiness for incremental refresh.

---

Common Mistakes & Troubleshooting

"View Native Query is always greyed out on my query"

This typically means either (a) your connector doesn't support folding at all (CSV, Excel, Web, and Folder connectors don't fold), or (b) you've accidentally broken folding in the very first step. Check whether your Source step references a Table object or something returned by a function that prevents folding. If you're connecting to SQL via Odbc.DataSource instead of Sql.Database, folding support is limited — use the native SQL connector where possible.

"My query folds but refresh is still slow"

Folding tells Power Query to send a query to the database — it doesn't guarantee that query runs efficiently. The generated SQL might not be using indexes, might be scanning tables, or might be generating a Cartesian product before filtering. Run SQL Profiler while the refresh executes, capture the exact SQL, and run it in SQL Server Management Studio with the actual execution plan enabled. Treat it as a database performance problem from that point.

"Incremental refresh isn't working even though my query seems to fold"

Check your RangeStart and RangeEnd parameter usage. These parameters must be used in a Table.SelectRows step that directly follows a foldable source. If they're used inside a custom function, or if they're referenced after a fold-breaking step, incremental refresh won't partition correctly. Also verify that both parameters are of type DateTime, not Date — Power BI injects DateTime values, and a type mismatch will either break folding or cause a runtime error.

"My filter step appears to fold but brings back more rows than expected"

Some connectors fold filter predicates but translate them in unexpected ways. For example, text comparisons might be case-insensitive in SQL Server depending on collation, but your M filter might expect case-sensitive behavior. The folded SQL respects the database's collation setting, while a locally-executed M filter is always case-sensitive by default. If you rely on case-sensitive text filtering, you may need to add explicit case normalization steps before the filter, accepting the fold-break trade-off, or handle the case sensitivity at the database level.

"I can't fold because my source is an Excel file"

You have three options: (1) Move the Excel data into a database and connect Power BI to the database, (2) use a Power BI Dataflow to ETL the Excel data and connect to the dataflow (dataflows can sometimes fold against Dataverse or Synapse backends), or (3) accept that this query won't fold and compensate with aggressive column selection and any source-level filtering that's available.

Warning: If a non-foldable Excel or CSV source is on the large side (say, over 500,000 rows), seriously consider whether maintaining it in Excel is the right long-term decision. The operational cost of pulling and processing a half-million row Excel file through the Power Query engine on every refresh cycle is significant, especially if you're running scheduled refreshes multiple times per day.

"A step that used to fold stopped folding after I updated Power BI Desktop"

This occasionally happens. Microsoft changes the M engine's behavior and folding support can expand or contract. If a previously folding step stops folding after an update, file it as a bug (Power BI's community forum and Ideas portal are the right venues). In the meantime, try using Value.NativeQuery to express that step directly in SQL as a workaround.

---

Summary & Next Steps

Query folding is one of the most impactful and least understood aspects of Power BI development. The developers who truly master it don't think of it as an advanced feature — they internalize it as a fundamental constraint that shapes how they design every query that touches a structured data source.

Here's what you should carry forward:

The core principle: Push as much computation as possible to the data source. Filters, joins, aggregations, and column selection that run at the database use that database's indexing, parallelism, and hardware. The same operations running locally in Power Query use your gateway machine's resources and move far more data across the network.

The diagnostic workflow: When you write a query against a foldable source, check "View Native Query" at each step. Know where your fold boundary is. Make a conscious decision about whether that boundary is in the right place, and restructure if it isn't.

The critical ordering rule: All foldable operations — especially filters that reduce row count — should come before any non-foldable step. Column reduction should happen immediately after the source. This isn't just a style preference; it's the architectural pattern that determines whether your model scales.

The incremental refresh dependency: If you're using or planning to use incremental refresh, your date-range filter steps must fold, full stop. Validate this explicitly, not by trusting Power BI's configuration UI, but by checking the actual SQL sent to the database.

From here, deepen your understanding of the data volume and refresh architecture problem space with Scheduled Refresh and Incremental Refresh Strategies, and explore how to combine Import and DirectQuery to build models that use query folding in both refresh and query-time contexts in Mastering Power BI Composite Models: Combining DirectQuery and Import Mode for Real-Time and Historical Data Analysis. For the performance optimization story at the reporting layer — once your data is loaded efficiently — Optimizing Power BI Report Performance: Query Reduction, Aggregations, and DirectQuery Tuning picks up exactly where this lesson leaves off.

Query folding is where the engineering rigor behind Power BI becomes visible. The developers who invest in understanding it build models that stay performant at scale, while everyone else wonders why their refresh times keep growing.