Power Query Problems

How to Reuse Excel Power Query with Different Files and Row Counts

Rana Garcia Oct 10, 2026 869 views

Question details

The user needs to apply an existing Power Query to new source workbooks that have different file names and varying row counts, while ensuring the destination table dynamically adjusts its size without breaking preformatted static content below it.

How to Reuse Excel Power Query with Different Files and Varying Row Counts

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating or transforming data from multiple workbooks where the column structures remain identical, but the file paths and total data rows change frequently.
Observed behavior: The output table must automatically expand or contract based on the new source's row count so that content below the totals row moves up or down accordingly.

Before you start

Ensure that all your target source files have consistent worksheet names, identical column headers, and the exact same column order to prevent query transformation errors.

Solution 1Recommended

Use a File Path Parameter to Dynamically Change Source Files

Creating a parameter allows you to quickly swap out the source file path without manually rewriting the Power Query M code.

By replacing the hardcoded file path in your query with a dynamic parameter, you can simply paste the new file name into the parameter box each time you need to process a new workbook. Because Power Query builds transformations around column names rather than fixed row numbers, the dynamic row counts will be handled naturally.

Create a New Parameter

Open the Power Query Editor. Go to the 'Home' tab, click 'Manage Parameters', and select 'New Parameter'. Name it 'FilePath' and set the 'Current Value' to the file path of your current source workbook.

Apply Parameter to the Source Step

In the 'Applied Steps' pane on the right, click the gear icon next to the 'Source' step. Change the file path dropdown from a static text string to your new 'FilePath' parameter.

Update and Refresh

Whenever you receive a new file, open 'Manage Parameters' from Excel, paste the new file's path into the parameter value, and click 'Refresh All' to update the query with the new data.

Use a File Path Parameter to Dynamically Change Source Files

Pro Tip: Make sure your new source file is closed in Excel before refreshing the query, as Power Query cannot always read from an actively open workbook.

Solution 2

Adjust Destination Table Properties for Dynamic Row Counts

Configure your Excel Table's external data properties so that adding or removing rows shifts the preformatted content below the table accordingly.

Solution 3

Use Folder-Based Source for Complete Automation

If you are processing multiple files or accumulating them over time, connecting to an entire folder skips the need to change individual file parameters.

Free Microsoft Office alternative

A Seamless, Free Alternative for Managing Complex Spreadsheets

If dealing with complex data connections and large workbooks becomes resource-intensive, WPS Office provides a lightweight, highly compatible alternative to Microsoft Office. It ensures your data processing tasks run smoothly without a hefty subscription fee.

1. Download and Install: Visit the official WPS website to download and install the free version of WPS Office on your device.
2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx workbooks directly without losing any of your original formatting.
3. Process Your Data: Utilize WPS Spreadsheet's robust built-in data tools to filter, pivot, and analyze your varying rows of data seamlessly.

Fully compatible with Microsoft Excel formats (.xls, .xlsx, .xlsm, .csv)Lightweight software that loads large datasets quickly and reliablyFamiliar ribbon interface requiring virtually no learning curveRich built-in data processing, filtering, and analytical tools

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Excel table expanding when refreshing Power Query with new data?

This usually happens if the Table properties are set to overwrite existing cells rather than insert new rows. Right-click the table, select Table > External Data Properties, and ensure 'Insert entire rows for new data, clear unused cells' is checked.

Can I automate the Power Query refresh process for multiple files?

Yes. While you can click 'Refresh All' manually in Excel, you can fully automate the process by using Microsoft Power Automate to trigger a data refresh whenever a new file is added to a specific SharePoint or OneDrive folder.

Will changing the source file break my Power Query if the column names are slightly different?

Yes. Power Query is highly case-sensitive and relies on exact column names. If a new source file has a column named 'sales' instead of 'Sales', the query will fail. Ensure your column headers are identical before refreshing.

Excel & Data Problems