Power Query Problems

Use Excel Table Values as Power Query SQL Parameters

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Use values from an Excel worksheet table to filter a SQL query in Power Query, including values from multiple rows.

Use Excel Table Values as Power Query SQL Parameters
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A worksheet table contains account and item values that should be passed to a database query.
Observed behavior
The query should read the required table columns and rows, then use those values to return matching database records.
Before you start

Confirm that the worksheet table is named AcctItem and identify its column names and the matching database columns. You also need a working database connection and permission to query the target table.

Solution 1Recommended

Build SQL value tuples from the Excel table

Use this approach when a SQL Server query must match multiple account and item pairs from an Excel table.

The example reads every non-null Account and Item pair rather than only the first row. It treats both columns as text and escapes single quotes in SQL string literals. Replace the server, database, table, and column names with your own.

Escaping text is not a substitute for a parameterized database process. Validate inputs and prefer a staging table or a properly parameterized database process when available, especially for untrusted data.

1
Check the worksheet table

Select a cell in the worksheet table and check Table Design > Table Name. Name it AcctItem and make sure it has columns named Account and Item.

2
Open a blank query

In Excel, select Data > Get Data > From Other Sources > Blank Query, then open Home > Advanced Editor.

3
Add the query

Replace the editor contents with the following M code, changing the placeholders and database identifiers to match your SQL Server source: let SourceTable = Excel.CurrentWorkbook(){[Name="AcctItem"]}[Content], Selected = Table.SelectColumns(SourceTable, {"Account", "Item"}), NonNull = Table.SelectRows(Selected, each [Account] <> null and [Item] <> null), SqlText = (value as any) as text => "N'" & Text.Replace(Text.From(value), "'", "''") & "'", Tuples = List.Transform(Table.ToRecords(NonNull), each "(" & SqlText([Account]) & ", " & SqlText([Item]) & ")"), Sql = if List.IsEmpty(Tuples) then "SELECT t.* FROM dbo.Transactions AS t WHERE 1 = 0" else "SELECT t.* FROM dbo.Transactions AS t WHERE EXISTS (SELECT 1 FROM (VALUES " & Text.Combine(Tuples, ", ") & ") AS v([Account], [Item]) WHERE t.[Account] = v.[Account] AND t.[Item] = v.[Item])", Result = Sql.Database("YOUR_SERVER", "YOUR_DATABASE", [Query = Sql]) in Result

4
Connect and refresh

Select Done, provide database credentials if prompted, and check the preview. Select Close & Load to return the results to Excel; use Data > Refresh All after changing the AcctItem table.

Build SQL value tuples from the Excel table
Adapt the query to your data: This example uses SQL Server syntax and text comparisons. Use suitable database types and validation for numeric or date values. If no non-null pairs are present, it returns no rows.
Free Microsoft Office alternative

Manage Excel-compatible workbooks with WPS Office

WPS Office is a free, lightweight alternative for everyday spreadsheet work. Its familiar interface and Excel-compatible formats make it easy to move workbooks between office suites. It does not fix Excel Power Query or replace the SQL query process described above.

Edit worksheet tables used to organize account and item valuesOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I reference a single value from the Excel table?

After loading the table with Excel.CurrentWorkbook(){[Name="AcctItem"]}[Content], use a row and column reference such as SourceTable{0}[Account]. Row indexes start at zero, so this reads only the first data row.

How does the query handle more than one account and item pair?

Table.ToRecords converts the selected rows into records, and List.Transform creates a SQL value tuple for each non-null pair. The SQL EXISTS condition then checks for a matching pair.

What happens if the worksheet table is empty?

The example sends a SELECT statement with WHERE 1 = 0, so the database returns no rows instead of an invalid empty VALUES clause.

Why does Power Query ask about privacy levels?

The query combines values from the current workbook with a database source. Review the prompts and set appropriate privacy levels for both sources before allowing Power Query to combine them.

Are escaped SQL strings the same as database parameters?

No. Escaping quotes helps form valid SQL text, but the values are still embedded in the command. For untrusted inputs or sensitive workflows, use validation and a staging table or properly parameterized database process.