Reusable Parameter Tables in Power Query: Dynamic Filtering That Still Folds

Light

A naive parameter table in Power Query will break query folding the moment you reference it in a filter, disabling pushdown and causing performance to tank on large DirectQuery or Import sources. This article shows, step by step, how to construct reusable, dynamic parameter tables—such as for user-driven filtering or dynamic SQL predicates—without sacrificing folding, and explains why most table-based parameterization patterns fail silently in production. By the end, you’ll be able to implement dynamic, reusable parameter tables that preserve folding for robust, scalable filtering scenarios.

Why Most “Dynamic” Parameter Tables Kill Query Folding

The textbook approach for parameterizing filters in Power Query is to create a table (or list) of values—perhaps from another query, or as a hard-coded input—and then filter your main query using this parameter table. This works perfectly in memory, but introduces a folding breaker the moment you pass a parameter table into a filter step. The classic example looks like this:

// Parameter table
let
    Values = #table({"Country"}, {{"USA"}, {"Canada"}})
in
    Values

// Main query
let
    Source = Sql.Database("Server", "DB"),
    Sales = Source{[Schema="dbo", Item="Fact_Sales"]}[Data],
    Filtered = Table.SelectRows(Sales, each List.Contains(Values[Country], [Country]))
in
    Filtered

This folds up to the Table.SelectRows step, then collapses to in-memory evaluation. The problem: List.Contains and references to in-memory tables inside a filter step can’t be translated to SQL or the underlying data source. Power Query pulls all rows into memory before filtering, negating the entire point of folding. If your fact table is even moderately large (say, 10M rows), this move takes you from query folding to full table scan and memory pressure with zero warning.

Most devs don’t notice this in Desktop, or with small tables, but it becomes a deployment killer in production. The failure is silent—no error, no warning, just a massive performance hit. The same applies to referencing a parameter table in a Merge: if the parameter table isn’t native/foldable, folding is gone.

The Only Reliable Folding-Safe Parameterization: Native Query Parameters and Query Folding Primitives

To preserve folding while allowing for dynamic, reusable parameters, you have only a few options that the engine can reliably push down:

• Native Query Parameters: Use M parameters (not tables/lists) that get substituted directly into SQL or the native query via the Value.NativeQuery function or parameterized connectors.
• Folding Primitives: Restrict parameter logic to folding-compatible M constructs: direct comparisons (=, <>, >, etc.), Text.Contains when mapped to LIKE, and, crucially, avoid any function that requires row-by-row in-memory evaluation (e.g., List.Contains, Table.SelectRows referencing an in-memory table, Table.Buffer).
• Parameter Expansion in SQL: Where you need a multi-value filter, compose a dynamic SQL statement with the parameter injected (but this is only folding-safe in Value.NativeQuery and can open you to SQL injection if not careful).

The key is that any reference to an in-memory object (table or list) in a filter step makes folding impossible, because the data source can’t “see” the M table to translate it into SQL. If you want to filter on more than one value, you must construct the filter in a way the folding engine understands—usually as a static list or by expanding the predicate into OR conditions.

Worked Example: Filtering Sales by a Dynamic List of Countries Without Breaking Folding

Take a model with a Fact_Sales table in SQL Server, and suppose you want to filter it by a dynamic list of countries that can be reused across multiple queries. The naive pattern is to build a parameter table and use List.Contains in Table.SelectRows, as shown above, which destroys folding. Instead, the folding-safe pattern is to:

1. Expose a text parameter for the user or logic to set (e.g., a comma-separated list: "USA,Canada").
2. Split this string into a list in M, but do not reference it directly in the filter step.
3. Construct a dynamic SQL WHERE clause, or, if possible, use Table.SelectRows with folding-compatible logic (only for simple equality, not list membership).

Here’s how that looks in practice using Value.NativeQuery:

// User parameter (text)
let
    CountryList = "USA,Canada"
in
    CountryList

// Build dynamic WHERE clause
let
    Countries = Text.Split(CountryList, ","),
    QuotedCountries = List.Transform(Countries, each "'" & _ & "'"),
    WhereClause = "WHERE Country IN (" & Text.Combine(QuotedCountries, ",") & ")"
in
    WhereClause

// Main query using Value.NativeQuery
let
    Source = Sql.Database("Server", "DB"),
    CountryList = "USA,Canada", // Could connect to a parameter
    Countries = Text.Split(CountryList, ","),
    QuotedCountries = List.Transform(Countries, each "'" & _ & "'"),
    WhereClause = "WHERE Country IN (" & Text.Combine(QuotedCountries, ",") & ")",
    Sql = "SELECT * FROM dbo.Fact_Sales " & WhereClause,
    Result = Value.NativeQuery(Source, Sql)
in
    Result

This approach keeps filtering in the SQL engine, fully preserves folding, and allows the parameter table (“list of countries”) to be reused and updated without breaking folding. However, it does mean you must manage SQL injection risk (only allow trusted values, sanitize inputs), and you lose the abstraction of M’s query steps. For most scenarios where you need a reusable, folding-safe multi-value parameter, this is the reliable pattern.

What If You Need to Reuse the Parameter Table Across Multiple Queries?

If you want to centralize your parameter logic (for example, read a list from SharePoint, Excel, or a configuration table) and have it drive multiple query filters, you must inject it into each native query you’re composing. Power Query has no built-in parameter expansion syntax for “IN” lists in folding-compatible M code. Attempting to join or merge with the parameter table will again break folding, as the merge can’t be pushed down unless both tables are native queries from the same source and the join is on indexed columns (rare for a parameter table).

The trade-off: to maximize reuse, keep parameter tables as primitive as possible (ideally, just text parameters), and push any multi-value logic into the query-building step. Don’t attempt to merge or filter using in-memory tables if folding is required. If you must use a parameter table, read it in via a query step, then materialize it as a text parameter for use in your native queries.

Why Table.Buffer and Table.AddIndexColumn Break Folding—And the Hidden Cost of “Just One More Step”

It’s tempting to preprocess your parameter table (add an index, buffer it, or munge it with custom logic) before using it in a filter. Each of these steps risks breaking folding, often at a different point in the pipeline than you expect. For example:

let
    ParameterTable = Table.AddIndexColumn(Values, "Index", 1, 1),
    // ... later ...
    Filtered = Table.SelectRows(Sales, each List.Contains(ParameterTable[Country], [Country]))
in
    Filtered

Both Table.AddIndexColumn and Table.Buffer force the parameter table to be evaluated in memory. When you then reference it in a filter, folding is irrevocably broken—even if the underlying source is foldable. The practical effect is that you can’t “improve” your parameter table with extra steps and expect folding to survive. If you need anything more complex than a scalar or a pure native query, folding is lost. The only exception is when both sources in a Merge are native, from the same source, and the join is simple. Even then, Power Query is conservative and folding may still break.

The Trade-off: Maintainability vs. Folding—And When to Accept an In-Memory Parameter Table

There are scenarios where you must choose between maintainability (user-friendly parameter queries, complex logic, or external configuration) and folding (performance, scale, DirectQuery compatibility). If your parameter logic is complex and folding is non-negotiable, you must push as much as possible into the data source—either with parameterized native queries, or (where the source supports it) views or stored procedures. If you can live with an in-memory filter (small dimension tables, infrequent refresh, Import mode), then a parameter table works fine. But for large fact tables, DirectQuery, or a need for scalable refresh, folding must win—which means table-based parameterization is off the table, and all filtering should be pushed down via folding-compatible primitives.

Checklist: How to Build Folding-Safe, Reusable Parameter Logic in Power Query

• Use scalar or text parameters for user input—avoid tables or lists unless they can be pushed into SQL directly.
• Build native queries (Value.NativeQuery) with injected parameter values for multi-value filters.
• Never reference an in-memory table or list in a Table.SelectRows or Merge step if folding is required.
• Verify folding after each parameterization step using “View Native Query” or Query Diagnostics (not just in Desktop, but in your deployed environment).
• Accept that preprocessing a parameter table (buffering, adding columns) will break folding; avoid unless you’re in Import mode and the data volume is trivial.
• Document the trade-off: if you need both reuse and folding, keep parameter logic as close to native SQL as possible.

The only reliable pattern for reusable, dynamic, folding-safe parameterization in Power Query is to keep parameters as scalars or strings and inject them into native queries, sacrificing some model abstraction for performance and scalability. Every other pattern risks an invisible folding break and a production performance crisis.