Combine Excel Files and Include New Rows with Power Query
Question details
The user needs to consolidate data from multiple Excel template files into a master workbook and include rows added later.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A manager collects similarly structured Excel files from team members and wants to refresh a consolidated master workbook as the files change.
- Observed behavior
- Standard workbook links can update existing cells but do not reliably include newly added rows.
Place the team files in a shared folder accessible to the person refreshing the master workbook. Use the same table name, column headers, and structure in each file, and keep the master workbook outside the source folder.
Combine the team files from a folder with Power Query
Use this method to consolidate consistently structured Excel files and bring in new rows when you refresh the query.
In each team workbook, select the data and choose Insert > Table. Give the tables the same name and make sure their column headers match. Team members should add new records inside their tables and save their files.
Open the master workbook and select Data > Get Data > From File > From Folder. Choose the shared folder containing the team files.
In the folder preview, select Combine & Transform Data. In the sample-file dialog, select the table that has the same name in each workbook, then confirm.
Review the combined columns in Power Query Editor, then select Home > Close & Load to add the consolidated data to the master workbook.
After team members save new or changed rows, open the master workbook and select Data > Refresh All. The query reads the source files again and updates the consolidated output.
Work with Excel Files in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move between office suites. Use Microsoft Excel for the Power Query folder-combine workflow described above.
Frequently Asked Questions
Will Power Query include rows added after the initial import?
Yes. Add the rows inside the source table, save the source workbook, and select Data > Refresh All in the master workbook.
Can Power Query include a new team file added to the folder?
Yes. Add the file to the connected folder with the same table name and structure as the other files, then select Data > Refresh All in the master workbook.
Why should the master workbook be outside the source folder?
Keeping it outside the folder prevents the folder query from trying to import the master workbook as another source file.
Does the master workbook update as soon as a team member saves a file?
Not automatically by default. Select Data > Refresh All to update the query, or configure the query's refresh properties in Excel if you want it to refresh at a specified time or when the workbook opens.
