Apply Shared Inventory and Date Filters to Multiple Excel Power Query Queries
Question details
Use one centrally maintained inventory list and date range across eight Excel 2016 Power Query queries while keeping their outputs separate.

- 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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




