Implementing Custom Query Folding Logic in M: Keeping Transformations Native to the Data Source

Introduction

Picture this: you've built a Power Query solution that pulls from a 50-million-row SQL Server database. Your report loads in seconds during development because you're working with a filtered sample. Then you deploy it, remove the row limit, and suddenly the refresh that should take two minutes is taking twenty-five. You open the Query Diagnostics pane and discover the awful truth — every transformation you wrote after that one Table.AddColumn call stopped folding. Power Query is pulling the entire table into memory and processing it locally. Congratulations, you've met the query folding cliff.

Query folding — the process by which Power Query translates M transformations back into native source queries — is one of the most impactful performance levers available to a data professional. When it works, your data source does the heavy lifting: filtering, sorting, grouping, and joining happen inside a database engine optimized for exactly that work. When it breaks, Power Query becomes a local processing engine wrestling with data volumes it was never designed to handle. The difference isn't marginal. It can be the difference between a functional solution and one that brings an organization's reporting infrastructure to its knees.

By the end of this lesson, you'll understand not just what query folding is, but how to actively engineer for it — diagnosing where folding breaks, restructuring queries to preserve it, and in some cases writing custom logic that explicitly hands work back to the source.

What you'll learn:

• How to diagnose whether a step is folding using Query Diagnostics and Value.NativeQuery
• Why certain M transformations break the fold chain and how to identify them
• How to restructure query steps to maximize folding coverage
• How to use Value.NativeQuery to inject native SQL when M's folding falls short
• How to implement a parameterized native query pattern safe for production use

---

Prerequisites

You should be comfortable with:

• Writing multi-step M queries using let...in expressions
• Connecting Power Query to SQL Server or another relational source
• Basic understanding of what query folding means conceptually
• Familiarity with SQL syntax (you don't need to be an expert, but you need to read it)

---

Why Folding Breaks (And Why It's Your Problem to Fix)

Before you can fix folding problems, you need to understand the mechanics of why they happen. Power Query's folding engine works by maintaining an internal representation of your transformation chain as something it can express in the target query language. As long as every step in your chain maps to an operation the source supports, the engine can keep building that representation.

The moment you introduce something it can't translate — a custom M function, certain text operations, a step that references a value from outside the source — the engine drops the translation entirely. Every subsequent step, no matter how simple, now runs locally. This is the folding cliff, and it's a cliff in the truest sense: once you go over it, you don't climb back up just by adding more foldable steps afterward.

Here's a concrete example. Suppose you're connecting to a SQL Server table called SalesTransactions with 40 million rows. You write this query:

let
    Source = Sql.Database("prod-server", "SalesDB"),
    SalesTransactions = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
    FilteredRows = Table.SelectRows(SalesTransactions, each [TransactionDate] >= #date(2023, 1, 1)),
    AddedYear = Table.AddColumn(FilteredRows, "FiscalYear", each Date.Year([TransactionDate])),
    CustomFlag = Table.AddColumn(AddedYear, "IsHighValue", each 
        if [Amount] > 10000 and Text.StartsWith([CustomerCode], "ENT") then true else false
    ),
    FinalGrouped = Table.Group(CustomFlag, {"FiscalYear", "IsHighValue"}, {{"TotalAmount", each List.Sum([Amount]), type number}})
in
    FinalGrouped

The FilteredRows step folds perfectly. AddedYear likely folds too — Date.Year has a SQL equivalent. But Text.StartsWith in the CustomFlag step? That may or may not fold depending on the connector version and the specific operation. If it doesn't, FinalGrouped definitely won't fold either, because it depends on CustomFlag. You're now grouping 40 million filtered rows in memory.

Critical insight: Folding isn't just about individual steps — it's about the chain. A non-folding step poisons every step that depends on it.

---

Diagnosing the Fold: Tools and Techniques

Using the Native Query Indicator

In Power Query Editor, right-click on any step in the Applied Steps panel. If you see "View Native Query" in the context menu and it's clickable (not grayed out), that step is folding. If it's grayed out, it isn't. This is your first diagnostic.

For steps that are folding, clicking "View Native Query" shows you exactly what SQL (or OData query, or whatever the native language is) Power Query is generating. This is invaluable. You might discover Power Query is generating a 47-join monster when a simple two-table join would suffice, which points you toward restructuring.

Query Diagnostics

For deeper analysis, go to the Tools tab in Power Query Editor and enable "Start Diagnostics." Run your refresh, then "Stop Diagnostics." This generates two tables: a summary and a detailed trace. The detailed trace shows you the actual queries sent to the source and the timing for each step. Look for steps where the Data Source Kind column shows your database but the query sent is unexpectedly broad — this often reveals partial folding where the filter didn't make it down.

Using `Table.IsEmpty` as a Folding Probe

Here's a technique experienced M developers use: wrap a suspicious step in Table.IsEmpty(). If this returns quickly, the step is likely folding (the database handles the empty-check efficiently). If it hangs, Power Query is probably pulling data locally. This isn't a production technique — it's a diagnostic probe you use during development and then remove.

The `Value.NativeQuery` Inspection Pattern

For the most precise diagnosis, you can use Value.NativeQuery to check what query the engine would generate for a given table expression:

// This isn't a full query — it's a diagnostic pattern
let
    Source = Sql.Database("prod-server", "SalesDB"),
    SalesTransactions = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
    FilteredRows = Table.SelectRows(SalesTransactions, each [TransactionDate] >= #date(2023, 1, 1)),
    // Check what SQL this generates:
    NativeSQL = Value.NativeQuery(FilteredRows, "SELECT 1") // This forces the engine to resolve FilteredRows as a subquery
in
    NativeSQL

Actually, the more direct approach: after writing a transformation chain, open the Advanced Editor and note which step you're testing. Right-click that step name in Applied Steps and use "View Native Query." The absence of this option is your signal to investigate.

---

The Anatomy of a Fold-Breaking Step

Understanding exactly which M operations break folding helps you make better architectural decisions. The full list varies by connector, but the common culprits fall into a few categories.

Custom Functions and Recursive Logic

Any invocation of a user-defined function breaks folding. The engine has no way to translate your custom M logic into SQL. This is the most common source of unintentional fold breaks:

// This breaks folding — the engine can't translate CleanCustomerName into SQL
CleanCustomerName = (name as text) => 
    Text.Trim(Text.Proper(Text.Replace(name, "  ", " "))),

AppliedClean = Table.TransformColumns(FilteredData, {{"CustomerName", CleanCustomerName}})

Accessing Non-Source Data Mid-Chain

If you reference a value that comes from outside the source — a parameter, a lookup table from a different source, a hardcoded list — this can break folding depending on how you use it. Scalar parameters often fold fine; joining to a local Excel table does not.

// This likely breaks folding — LocalExclusions comes from Excel, not SQL Server
LocalExclusions = Excel.CurrentWorkbook(){[Name="ExclusionList"]}[Content],
Filtered = Table.SelectRows(SQLTable, each not List.Contains(LocalExclusions[CustomerID], [CustomerID]))

Certain M Functions Without SQL Equivalents

Some M functions simply don't have direct equivalents in common SQL dialects. Table.Pivot, Table.UnpivotOtherColumns, complex Table.NestedJoin configurations, and certain date functions fall into this category. The connector's folding capability also matters — the SQL Server connector folds much more aggressively than the OData connector.

Type Operations After Promotion

Here's a subtle one. If you use Table.TransformColumnTypes to change a column type and the type conversion isn't natively expressible (like converting a text column to a decimal with specific culture settings), the fold chain breaks. Always check whether type transformations are folding.

---

Restructuring Queries to Preserve Folding

Now that you understand what breaks folding, let's talk about the most powerful technique: restructuring your query so that all foldable operations happen on the source-side of the chain, and non-foldable operations happen after you've already reduced your dataset as much as possible.

The "Fold First, Transform Last" Principle

The golden rule is straightforward: push every operation that can fold to the earliest possible point in your chain. Do all your filtering, joining, grouping, and sorting at the source. Then bring the reduced dataset across the network and do your custom transformations locally on the smaller result.

Here's the bad version — transformation interleaved with filterable operations:

// PROBLEMATIC: Custom column added before grouping
let
    Source = Sql.Database("prod-server", "SalesDB"),
    SalesTransactions = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
    FilteredRows = Table.SelectRows(SalesTransactions, each [TransactionDate] >= #date(2023, 1, 1)),
    
    // This custom function breaks folding here
    AddedSegment = Table.AddColumn(FilteredRows, "Segment", 
        each DetermineSegment([Amount], [CustomerTier])),
    
    // These steps now run locally on 15 million rows
    GroupedByRegion = Table.Group(AddedSegment, {"Region"}, 
        {{"TotalAmount", each List.Sum([Amount]), type number}}),
    SortedResults = Table.Sort(GroupedByRegion, {{"TotalAmount", Order.Descending}})
in
    SortedResults

Here's the restructured version:

// BETTER: All foldable operations first, custom logic applied to small result
let
    Source = Sql.Database("prod-server", "SalesDB"),
    SalesTransactions = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
    FilteredRows = Table.SelectRows(SalesTransactions, each [TransactionDate] >= #date(2023, 1, 1)),
    
    // Group and sort fold back to SQL — operating on source data
    GroupedByRegion = Table.Group(FilteredRows, {"Region", "Amount", "CustomerTier"}, 
        {{"TotalAmount", each List.Sum([Amount]), type number}}),
    SortedResults = Table.Sort(GroupedByRegion, {{"TotalAmount", Order.Descending}}),
    
    // Now apply custom logic to the much smaller aggregated result (maybe 20 rows)
    AddedSegment = Table.AddColumn(SortedResults, "Segment", 
        each DetermineSegment([TotalAmount], [CustomerTier]))
in
    AddedSegment

The second version sends a GROUP BY and ORDER BY to SQL Server, brings back maybe 20 grouped rows, and then applies the custom function locally on 20 rows instead of 15 million. The performance difference is orders of magnitude.

Splitting Queries at the Fold Boundary

Sometimes the right architecture is splitting your work into two separate queries in the Power Query tree. Create one query that does all source-side work and terminates at the fold boundary. Then create a second query that references the first and applies local transformations.

// Query 1: SalesAggregated (folds completely to SQL)
let
    Source = Sql.Database("prod-server", "SalesDB"),
    SalesTransactions = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
    Filtered = Table.SelectRows(SalesTransactions, 
        each [TransactionDate] >= #date(2023, 1, 1) and [Status] = "Completed"),
    Grouped = Table.Group(Filtered, {"CustomerID", "Region", "ProductCategory"}, {
        {"TotalRevenue", each List.Sum([Amount]), type number},
        {"TransactionCount", each Table.RowCount(_), type number},
        {"AvgOrderValue", each List.Average([Amount]), type number}
    })
in
    Grouped

// Query 2: SalesWithSegments (applies M-only logic to small aggregated result)
let
    Source = SalesAggregated,
    AddedSegment = Table.AddColumn(Source, "CustomerSegment", 
        each 
            if [TotalRevenue] > 500000 then "Enterprise"
            else if [TotalRevenue] > 100000 then "Mid-Market"
            else "SMB"
    ),
    AddedEngagementScore = Table.AddColumn(AddedSegment, "EngagementScore",
        each [TransactionCount] * 0.3 + ([AvgOrderValue] / 1000) * 0.7
    )
in
    AddedEngagementScore

The second query references the first, and Power Query is smart enough to handle this correctly. The aggregation folds to SQL; the segment logic runs locally on the compact result.

---

Using `Value.NativeQuery` for Explicit Native Queries

When restructuring isn't enough — or when you need precise control over what the source executes — Value.NativeQuery lets you drop raw native SQL (or the source's native query language) directly into your M expression.

The Basic Pattern

let
    Source = Sql.Database("prod-server", "SalesDB"),
    NativeResult = Value.NativeQuery(
        Source,
        "
        SELECT 
            c.CustomerID,
            c.CustomerName,
            c.Region,
            SUM(t.Amount) AS TotalRevenue,
            COUNT(*) AS TransactionCount,
            AVG(t.Amount) AS AvgOrderValue
        FROM dbo.SalesTransactions t
        INNER JOIN dbo.Customers c ON t.CustomerID = c.CustomerID
        WHERE t.TransactionDate >= '2023-01-01'
            AND t.Status = 'Completed'
            AND c.AccountType IN ('Premium', 'Enterprise')
        GROUP BY c.CustomerID, c.CustomerName, c.Region
        HAVING SUM(t.Amount) > 10000
        ORDER BY TotalRevenue DESC
        "
    )
in
    NativeResult

This query executes exactly as written on the server. No translation, no ambiguity. You get the result back as a Power Query table and can apply further M transformations to it.

Warning: Value.NativeQuery bypasses Power Query's type inference. You'll often need to follow this step with Table.TransformColumnTypes to set proper column types. Also note that further steps after Value.NativeQuery will not fold — you've already exited the folding chain.

Parameterized Native Queries

The dangerous version of Value.NativeQuery looks like this — and you should never do it:

// DANGEROUS: SQL injection vulnerability
let
    UserInput = "2023-01-01", // imagine this comes from a user parameter
    Source = Sql.Database("prod-server", "SalesDB"),
    BadQuery = Value.NativeQuery(
        Source,
        "SELECT * FROM dbo.SalesTransactions WHERE TransactionDate >= '" & UserInput & "'"
    )
in
    BadQuery

String concatenation into SQL is an injection vulnerability. The safe version uses parameterized queries, which Value.NativeQuery supports natively:

// SAFE: Parameterized query
let
    StartDateParam = #date(2023, 1, 1),
    EndDateParam = #date(2023, 12, 31),
    RegionParam = "APAC",
    
    Source = Sql.Database("prod-server", "SalesDB"),
    
    SafeQuery = Value.NativeQuery(
        Source,
        "
        SELECT 
            t.TransactionID,
            t.CustomerID,
            t.Amount,
            t.TransactionDate,
            t.Region,
            t.ProductCategory,
            t.Status
        FROM dbo.SalesTransactions t
        WHERE t.TransactionDate BETWEEN @StartDate AND @EndDate
            AND t.Region = @Region
            AND t.Status = 'Completed'
        ",
        [
            StartDate = StartDateParam,
            EndDate = EndDateParam,
            Region = RegionParam
        ]
    ),
    
    TypedResult = Table.TransformColumnTypes(SafeQuery, {
        {"TransactionID", type text},
        {"CustomerID", type text},
        {"Amount", type number},
        {"TransactionDate", type date},
        {"Region", type text},
        {"ProductCategory", type text},
        {"Status", type text}
    })
in
    TypedResult

The third argument to Value.NativeQuery is a record of named parameters. The M runtime passes these to the data source as proper parameterized query inputs — the values are never string-concatenated into the SQL text. This is the only safe way to incorporate dynamic values into a native query.

Tip: Not all connectors support parameterized Value.NativeQuery. SQL Server does, Oracle does, PostgreSQL does (via the ODBC connector). Check your connector's documentation. If parameterization isn't supported, move the filtering to M-level Table.SelectRows calls instead, which will fold when possible.

---

Advanced Pattern: Conditionally Native Queries

In production Power Query solutions, you often need to balance portability with performance. A query that uses Value.NativeQuery with SQL Server-specific syntax won't work if someone later needs to point it at PostgreSQL. Here's a pattern that gives you the best of both worlds:

let
    // Configuration: set to true for performance-critical production environments
    UseNativeQuery = true,
    
    Source = Sql.Database("prod-server", "SalesDB"),
    
    // Path 1: Native SQL for maximum performance
    NativePath = Value.NativeQuery(
        Source,
        "
        SELECT 
            YEAR(TransactionDate) AS FiscalYear,
            DATEPART(QUARTER, TransactionDate) AS FiscalQuarter,
            Region,
            ProductCategory,
            SUM(Amount) AS TotalRevenue,
            COUNT(DISTINCT CustomerID) AS UniqueCustomers,
            AVG(Amount) AS AvgTransactionValue
        FROM dbo.SalesTransactions
        WHERE Status = 'Completed'
            AND TransactionDate >= DATEADD(YEAR, -2, GETDATE())
        GROUP BY 
            YEAR(TransactionDate),
            DATEPART(QUARTER, TransactionDate),
            Region,
            ProductCategory
        "
    ),
    
    // Path 2: Pure M for portability (folds where possible)
    MPath = 
        let
            BaseTable = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
            Filtered = Table.SelectRows(BaseTable, 
                each [Status] = "Completed" and 
                     [TransactionDate] >= Date.AddYears(Date.From(DateTime.LocalNow()), -2)),
            Grouped = Table.Group(Filtered, {"Region", "ProductCategory"}, {
                {"FiscalYear", each Date.Year(List.Min([TransactionDate])), type number},
                {"TotalRevenue", each List.Sum([Amount]), type number},
                {"UniqueCustomers", each List.Count(List.Distinct([CustomerID])), type number},
                {"AvgTransactionValue", each List.Average([Amount]), type number}
            })
        in
            Grouped,
    
    Result = if UseNativeQuery then NativePath else MPath
in
    Result

This pattern lets you toggle between implementations with a single variable change. In practice, you'd often drive UseNativeQuery from a Power Query parameter, making it easy to switch without editing query code.

---

Implementing a Reusable Folding-Aware Filter Function

One pattern that comes up constantly in production solutions is needing to apply a set of dynamic filter conditions to a table. The naive implementation breaks folding; the right implementation preserves it.

Here's a real-world scenario: a reporting solution where users can select multiple filter criteria (date range, region list, product categories), and you need these to fold to the database.

// Helper function: BuildDynamicFilter
// Applies a record of filter conditions in a fold-friendly way
// Conditions record format: [DateFrom, DateTo, Regions, ProductCategories, MinAmount]
let
    BuildDynamicFilter = (sourceTable as table, conditions as record) as table =>
        let
            // Apply each filter only if the condition value is provided
            // Each step can fold independently if the previous one folded
            
            AfterDateFrom = 
                if Record.HasFields(conditions, "DateFrom") and conditions[DateFrom] <> null
                then Table.SelectRows(sourceTable, each [TransactionDate] >= conditions[DateFrom])
                else sourceTable,
            
            AfterDateTo = 
                if Record.HasFields(conditions, "DateTo") and conditions[DateTo] <> null
                then Table.SelectRows(AfterDateFrom, each [TransactionDate] <= conditions[DateTo])
                else AfterDateFrom,
            
            AfterRegions = 
                if Record.HasFields(conditions, "Regions") and 
                   conditions[Regions] <> null and 
                   List.Count(conditions[Regions]) > 0
                then Table.SelectRows(AfterDateTo, each List.Contains(conditions[Regions], [Region]))
                else AfterDateTo,
            
            AfterCategories = 
                if Record.HasFields(conditions, "ProductCategories") and 
                   conditions[ProductCategories] <> null and
                   List.Count(conditions[ProductCategories]) > 0
                then Table.SelectRows(AfterRegions, each List.Contains(conditions[ProductCategories], [ProductCategory]))
                else AfterRegions,
            
            AfterMinAmount = 
                if Record.HasFields(conditions, "MinAmount") and conditions[MinAmount] <> null
                then Table.SelectRows(AfterCategories, each [Amount] >= conditions[MinAmount])
                else AfterCategories
        in
            AfterMinAmount
in
    BuildDynamicFilter

And the usage:

let
    Source = Sql.Database("prod-server", "SalesDB"),
    BaseTable = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
    
    UserFilters = [
        DateFrom = #date(2023, 1, 1),
        DateTo = #date(2023, 12, 31),
        Regions = {"APAC", "EMEA"},
        ProductCategories = {"Software", "Services"},
        MinAmount = 5000
    ],
    
    FilteredData = BuildDynamicFilter(BaseTable, UserFilters),
    
    // This grouping will fold because FilteredData folds
    Aggregated = Table.Group(FilteredData, {"Region", "ProductCategory"}, {
        {"TotalRevenue", each List.Sum([Amount]), type number},
        {"DealCount", each Table.RowCount(_), type number}
    })
in
    Aggregated

Important note about List.Contains and folding: Table.SelectRows with List.Contains against a list literal does fold on the SQL Server connector — it translates to an IN (...) clause. However, if the list comes from a dynamic source (another table, a function result), it may not fold. Always verify with the "View Native Query" check.

---

Hands-On Exercise

Scenario

You're building a sales performance dataset for a financial services company. Their transaction database on SQL Server contains a dbo.Trades table (30 million rows) with columns: TradeID, TraderID, TradeDate, Instrument, AssetClass, Notional, GrossProfit, Desk, CounterpartyID, and Status.

There's also a dbo.Traders table with: TraderID, TraderName, SeniorityLevel, DeskManager, and HireDate.

Your task is to build a Power Query solution that:

1. Filters trades from the last 3 years with Status = 'Settled'
2. Joins to the Traders table
3. Aggregates by Desk, AssetClass, and SeniorityLevel
4. Calculates total notional, total gross profit, trade count, and average profit per trade
5. Adds a custom profitability tier (a label based on average profit thresholds — this can't fold)

The constraint: the aggregation and filtering must fold to SQL Server. The profitability tier logic runs locally on the aggregated result.

Your Implementation

// Step 1: Set up source connections
let
    Source = Sql.Database("prod-server", "TradingDB"),
    
    // Step 2: Reference source tables (these reference steps fold)
    TradesTable = Source{[Schema="dbo", Item="Trades"]}[Data],
    TradersTable = Source{[Schema="dbo", Item="Traders"]}[Data],
    
    // Step 3: Filter trades — this folds to SQL WHERE clause
    ThreeYearsAgo = Date.AddYears(Date.From(DateTime.LocalNow()), -3),
    FilteredTrades = Table.SelectRows(TradesTable, 
        each [Status] = "Settled" and [TradeDate] >= ThreeYearsAgo),
    
    // Step 4: Join to traders — this folds to a SQL JOIN
    // Note: Power Query uses left outer join by default; specify the join kind
    JoinedData = Table.Join(
        FilteredTrades, "TraderID",
        TradersTable, "TraderID",
        JoinKind.Inner
    ),
    
    // Step 5: Select only needed columns before grouping (reduces data movement)
    ColumnsNeeded = Table.SelectColumns(JoinedData, {
        "Desk", "AssetClass", "SeniorityLevel",
        "Notional", "GrossProfit", "TradeID"
    }),
    
    // Step 6: Group and aggregate — this folds to SQL GROUP BY
    // At this point we're still in foldable territory
    Aggregated = Table.Group(ColumnsNeeded, 
        {"Desk", "AssetClass", "SeniorityLevel"}, 
        {
            {"TotalNotional", each List.Sum([Notional]), type number},
            {"TotalGrossProfit", each List.Sum([GrossProfit]), type number},
            {"TradeCount", each Table.RowCount(_), Int64.Type},
            {"AvgProfitPerTrade", each List.Average([GrossProfit]), type number}
        }
    ),
    
    // Step 7: NOW we leave the folded zone
    // Aggregated result is maybe a few hundred rows — local processing is fine
    
    // Add profitability tier — custom logic, won't fold (and doesn't need to)
    AddedTier = Table.AddColumn(Aggregated, "ProfitabilityTier", 
        each 
            if [AvgProfitPerTrade] >= 50000 then "Tier 1 - Premier"
            else if [AvgProfitPerTrade] >= 20000 then "Tier 2 - Strong"
            else if [AvgProfitPerTrade] >= 5000 then "Tier 3 - Standard"
            else "Tier 4 - Under Review",
        type text
    ),
    
    // Add a return-on-notional metric (simple arithmetic, could fold, but moot at this point)
    AddedRON = Table.AddColumn(AddedTier, "ReturnOnNotional", 
        each if [TotalNotional] = 0 then 0 
             else Number.Round([TotalGrossProfit] / [TotalNotional] * 100, 4),
        type number
    ),
    
    // Final sort — running locally on small result, that's fine
    SortedResult = Table.Sort(AddedRON, {
        {"TotalGrossProfit", Order.Descending}
    })

in
    SortedResult

Verify your work: Right-click the Aggregated step in Applied Steps. You should see "View Native Query" as a clickable option, and the generated SQL should include WHERE, JOIN, and GROUP BY clauses. Right-click AddedTier — it should be grayed out, confirming the local processing boundary is exactly where you intended it.

Challenge extension: Modify this query to use Value.NativeQuery for the entire filtering, joining, and aggregation phase. Make the date range parameterized using the safe parameterization approach. Verify that the result set is identical.

---

Common Mistakes & Troubleshooting

"View Native Query is grayed out everywhere"

This usually means either your connector doesn't support folding (CSV files, local Excel, SharePoint lists) or you've accidentally broken the fold at step one. Check whether you're actually connecting to a relational source via the correct connector. Sometimes developers connect to SQL Server via ODBC instead of the native connector, losing folding capabilities.

"My query folds in development but not in production"

Development environments often use SQL Server Developer Edition with the same feature set. But if production uses a linked server, a view, or a synonym that points to a different database, the connector's behavior can change. Also check whether query folding hints differ between versions of the on-premises data gateway — the gateway version affects which connector behaviors are available during scheduled refresh.

"Adding a parameter breaks my folding"

Power Query parameters fold fine when used as scalar comparisons. What doesn't fold is using ParameterName as a list in List.Contains. If you have a multi-value parameter (comma-separated text that you split into a list), you'll need to split it and use it carefully. A safer pattern is to use multiple individual parameters and build your filter conditions explicitly.

"My join doesn't appear in the native query"

When you join two tables from the same source using Table.Join or Table.NestedJoin followed by Table.ExpandTableColumn, this should fold to a SQL JOIN. Common reasons it doesn't:

• One of the tables came through a non-foldable step (the contamination problem)
• You're using Table.NestedJoin without expanding immediately after
• The join key columns have different data types between the two tables, forcing a local type coercion

Check each table independently — can each one individually "View Native Query"? If so, join them and check again. If the join breaks folding, the problem is likely in the join operation itself.

"Value.NativeQuery returns an error about parameters"

On some connectors (particularly ODBC), Value.NativeQuery parameterization uses ? placeholders instead of named parameters. Check your connector's documentation. For SQL Server via the native connector, named parameters with @ParameterName syntax work correctly. For ODBC, you may need to use positional parameters.

The Intermediate Buffer Trap

Table.Buffer forces evaluation and stores the result in memory, breaking any subsequent folding. Many developers use Table.Buffer to fix performance issues (it can help with some re-evaluation problems) without realizing it permanently ends the fold chain for everything downstream. Only buffer tables after you've already done all the source-side work.

// Don't do this mid-chain if you still need downstream operations to fold
EarlyBuffer = Table.Buffer(FilteredRows), // Folding is now dead for everything after this
GroupedResult = Table.Group(EarlyBuffer, ...) // This runs locally

---

Summary & Next Steps

Query folding is one of the few areas in data engineering where the architecture of your solution directly determines whether it's viable at production scale. The principles we've covered aren't edge cases — they're the difference between a solution that handles 50 million rows gracefully and one that times out.

Here's what to carry forward:

The fold chain is everything. A single non-foldable step poisons every downstream step. Your job is to place that boundary intentionally, after all heavy lifting is done on the source side.

Diagnose before you optimize. Use "View Native Query" and Query Diagnostics to understand what's actually happening before you start restructuring. Sometimes what looks like a folding problem is actually a connector quirk with a simple fix.

Value.NativeQuery is a precision tool, not a general solution. Use it when M's automatic translation isn't generating efficient enough SQL, or when you need database-specific features like window functions or CTEs. Always parameterize dynamic values.

The "fold first, transform last" principle applies to every query you write. Make it a habit: before you add a custom column or call a helper function, ask yourself whether there are still foldable operations waiting downstream. If yes, reorder.

Where to Go Next

The natural continuation from this lesson is exploring incremental refresh in Power BI — which depends entirely on query folding working correctly on your date/time columns. If folding is broken, incremental refresh silently falls back to full refresh and you won't know why.

You should also dig into query plan analysis using SQL Server's Execution Plan viewer in parallel with Power Query's native query output. Understanding what the database does with the SQL Power Query generates gives you the full picture and helps you write better native queries when needed.

Finally, explore connector extensibility — if you're working with a data source that has poor folding support, the Power Query SDK lets you build custom connectors that implement their own folding logic. That's an advanced topic, but understanding the M folding architecture you've learned here is the foundation you'll need to approach it.