When working with Power BI or Excel through Power Query, you may encounter scenarios where you need to filter SQL Server data using a list of values. For instance, you may want to dynamically load only records that match a list of customer IDs, product SKUs, or regions. This is typically done using the SQL WHERE ... IN (...) clause. However, passing a list of values from Power Query into a native SQL query isn’t straightforward due to the way parameter folding works.

This article focuses on SQL Server and Azure SQL, where the solution relies on the OPENJSON function available in SQL Server 2016 and newer. If you’re using another SQL platform, see the section at the end of this article for notes on alternatives and caveats.

Our use case for this exercise is going to be: Dynamic Filtering with a List of SKUs.

Why Passing a List Directly to Value.NativeQuery Doesn’t Work

Although Value.NativeQuery supports parameters, it only accepts scalar values — not lists or tables. If you try to pass a list directly into an IN (...) clause (e.g., @skuList = {"SKU001", "SKU002"}), you’ll encounter an error like:

No mapping exists from object type Microsoft.Mashup.Engine1.Library.Common.DataReaderDbDataReader to a known managed provider native type.

This is because Power Query cannot serialize M lists into a format the SQL provider understands. Instead, we need a workaround that packages the list as a string — specifically a JSON array.

Let’s say you have a list of product SKUs in Power Query, and you’d like to use this list in a SQL query like:

SELECT * FROM Products WHERE SKU IN ('SKU001', 'SKU002', 'SKU003')

Power Query does not natively allow you to inject a list into a native SQL query using IN (...) syntax directly. Here’s how to work around it using the parameters argument of Value.NativeQuery.

Step-by-Step Using OPENJSON and JSON Parameters

1. Define Your List from a Dynamic Source

Start by getting your list from another query or dynamic source, such as an Excel table:

let
    SKUListQuery = Excel.CurrentWorkbook(){[Name="SKUList"]}[Content],
    skuList = Table.Column(SKUListQuery, "SKU")
in
    skuList

2. Build SQL with a Token Placeholder

Prepare the SQL using a token placeholder for the list, combined with OPENJSON in the WHERE clause:

"SELECT * FROM Products WHERE SKU IN (SELECT [value] FROM OPENJSON(@skuJson))"

In this syntax, OPENJSON(@skuJson) parses the JSON array passed from Power Query into a table format with a default column named [value]. Each element in your list becomes a row, and you can filter the main table using WHERE ... IN (SELECT [value] FROM ...). This is a clean and safe way to perform list filtering server-side using JSON input.

3. Use OPENJSON in SQL with a JSON Text Parameter

Convert the list into a JSON text string and use SQL Server’s OPENJSON to unpack it in the query:

let
    Source = Sql.Database("your_server", "your_database"),
    SKUListQuery = Excel.CurrentWorkbook(){[Name="SKUList"]}[Content],
    skuList = Table.Column(SKUListQuery, "SKU"),
    jsonBinary = Json.FromValue(skuList),
    jsonText = Text.FromBinary(jsonBinary, TextEncoding.Utf8),
    query = "
        SELECT * 
        FROM Products AS p
        WHERE p.SKU IN (SELECT [value] FROM OPENJSON(@skuJson))
    ",
    Result = Value.NativeQuery(Source, query, [skuJson = jsonText])
in
    Result

🛈 This workaround is inspired by Pawar BI’s article on query folding with JSON parameters in Power Query. The origin of the OPENJSON trick is unclear, but it’s become a highly effective and accepted technique.

This approach leverages query folding and avoids manual string injection.

Beyond SQL Server

This technique is specific to SQL Server and Azure SQL, where OPENJSON is available. Other platforms like PostgreSQL, MySQL, or Snowflake don’t support OPENJSON, so you’ll need alternate approaches:

• You can serialize the list to a comma-separated string using Text.Combine, and inject it directly into the query:

let
    Source = Sql.Database("your_server", "your_database"),
    SKUListQuery = Excel.CurrentWorkbook(){[Name="SKUList"]}[Content],
    skuList = Table.Column(SKUListQuery, "SKU"),
    quoted = List.Transform(skuList, each "'" & Text.Replace(_, "'", "''") & "'"),
    joined = Text.Combine(quoted, ", "),
    query = "SELECT * FROM Products WHERE SKU IN (" & joined & ")",
    Result = Value.NativeQuery(Source, query)
in
    Result

This will generate a query like:

SELECT * FROM Products WHERE SKU IN ('SKU001', 'SKU002', 'SKU003')

However, this form of manual string injection is error-prone. You must handle escaping for quotes and other special characters carefully when using this approach of Text.Combine.

Conclusion

Passing a list to SQL using IN (...) via Power Query is possible using a JSON-based workaround — but only with SQL Server. By encoding your list as a UTF-8 JSON string and unpacking it with OPENJSON, you can safely inject dynamic filters into native SQL — while preserving query folding in many cases. This method allows for dynamic and safe query execution with proper folding support — making it a powerful technique for efficient filtering directly at the data source.