Power Query Problems

Apply Shared Inventory and Date Filters to Multiple Excel Power Query Queries

Jason MillerJason Miller Nov 13, 2026 868 views

Question details

Use one centrally maintained inventory list and date range across eight Excel 2016 Power Query queries while keeping their outputs separate.

Apply Shared Inventory and Date Filters to Multiple Excel Power Query Queries
Product
Microsoft Excel 2016 Power Query with SQL Server data sources
Device & OS
not provided
Scenario
Eight queries connect to different SQL Server inventory tables with different column structures and nonunique inventory IDs across tables.
Observed behavior
Inventory and date filters currently need to be updated separately in every query. Combining the tables is unsuitable because joins could create many-to-many results.
Before you start

Identify the inventory ID and date columns in each query, including their data types. Confirm whether an inventory ID should match across all tables or whether each selection also needs a source-table identifier.

Solution 1Recommended

Reference one inventory filter list and shared date parameters from every query

Use this approach to update filter values centrally without appending or merging the eight inventory tables.

Power Query parameters hold individual values. For multiple inventory values, create a separate query that returns a list, then reference that list from each SQL Server query. Keep the eight output queries separate.

Each query still needs its own filter step, but the selected inventory values and date boundaries are maintained only once.

1
Create the inventory filter table

On an Excel worksheet, enter the desired inventory IDs under a column named InventoryID. Select the range, choose Insert > Table, and confirm that the table has headers. On Table Tools > Design, set the table name to InventoryFilter.

2
Convert the table into a shared list query

Select a cell in InventoryFilter and choose Data > From Table/Range, or From Table in some Excel 2016 builds. In Power Query Editor, name the query FilterIDs. Set InventoryID to the same data type used by the source queries, then right-click its column header and choose Drill Down to return a list. Choose Home > Close & Load > Close & Load To, then select Only Create Connection. Remove blank entries from the worksheet filter table so they do not become unintended filter values.

3
Create the shared date parameters

Open a query in Power Query Editor and choose Home > Manage Parameters > New Parameter. Create StartDate and EndDate, set both to the Date type, and enter the desired boundaries. These parameters can be referenced by all queries in the workbook.

4
Add the shared filter to each SQL Server query

In Power Query Editor, select the first SQL Server query and open Home > Advanced Editor. Add a step after the existing source or transformation step, adapting this example: FilteredRows = Table.SelectRows(#"Previous Step", each List.Contains(FilterIDs, [InventoryID]) and Date.From([InventoryDate]) >= StartDate and Date.From([InventoryDate]) <= EndDate). Replace #"Previous Step" with the actual preceding step name and replace InventoryID and InventoryDate with that query's column names. Separate the new step from the preceding step with a comma, and change the final in expression to FilteredRows. Repeat for the other seven queries. This example includes the entire EndDate by comparing the date portion of each value; it assumes valid, non-null date values.

5
Update the values and refresh all outputs

For future changes, edit the IDs in the InventoryFilter worksheet table and change StartDate or EndDate through Home > Manage Parameters in Power Query Editor. Apply the parameter changes with Close & Load, then return to Excel and choose Data > Refresh All. Each output query will use the same updated filter inputs.

Reference one inventory filter list and shared date parameters from every query
Check ID scope and SQL Server performance: A shared list containing an ID such as 123 will match that ID in every source table. If the selection is table-specific, use a filter table with both a source identifier and InventoryID, and have each query select its relevant filter rows before building the list. Also check query folding: a worksheet-based List.Contains filter or date conversion may run locally rather than in SQL Server. If a Formula.Firewall error appears, review Data > New Query > Query Options > Privacy and the data sources' privacy settings; do not disable privacy protection indiscriminately.
Free Microsoft Office alternative

Use WPS Spreadsheets to Review Exported Inventory Results

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel .xlsx and .xls files, making it easy to move ordinary inventory reports between applications. Use it to review, sort, and filter exported results; it is not a replacement for Excel 2016's Power Query parameter and SQL Server refresh workflow.

Review and filter inventory results exported from your Excel queries.Supports Microsoft Excel .xlsx and .xls workbook formats.Familiar spreadsheet controls make report migration straightforward.Free, lightweight tools for everyday inventory report editing.
microsoft office alternative - wps office

Frequently Asked Questions

Can one Power Query parameter contain several inventory IDs?

A standard parameter represents one value, not a multi-select inventory list. For several IDs, create an Excel table, import it through Data > From Table/Range, and use Drill Down on the ID column to create a list query. Reference that query with List.Contains in each inventory query.

Do I need to merge or append the eight SQL Server queries?

No. Shared parameters and a shared filter-list query can feed eight separate output queries. Append stacks rows and may require schema alignment; merge joins rows and can multiply results when keys have multiple matches. Neither operation is needed just to share filter values.

What if the same inventory ID belongs to different tables?

If the same ID should be selected everywhere, a shared ID list is sufficient. If selections are table-specific, add a source-table column to the Excel filter table. In each query, filter that input table to the appropriate source, then extract its InventoryID list before applying List.Contains.

Will changing the filter table immediately update all eight outputs?

No. After editing the worksheet filter table, choose Data > Refresh All to refresh the dependent queries. For date parameters, open Power Query Editor > Home > Manage Parameters, change the values, apply the changes, and refresh the workbook outputs.

Why might a shared list filter slow down SQL Server queries?

List.Contains with a worksheet-sourced list may prevent that filter from folding into SQL Server, causing more rows to be processed locally. In Power Query Editor, right-click an applied step and check View Native Query where available. Apply foldable restrictions early, and consider a server-side filtering design for large datasets.