How to Automate Combining Weekly Excel Files with Power Query
Question details
The user needs to automate the process of combining recurring weekly Excel workbooks into a master dataset, handling inconsistent headers, and filtering specific records.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging multiple weekly reports into a centralized master dataset without manual copying and pasting.
- Observed behavior
- Currently seeking a reliable automation method to pull data from a folder, map source columns to a standard schema, filter data, and append results dynamically as new files arrive.
Ensure all your weekly Excel files are saved in a single, dedicated folder and that no unrelated files or different formats are present in that directory.
Use Power Query's From Folder Connector
Utilize the 'Get Data From Folder' feature to automatically append all files in a directory into one cohesive dataset.
Power Query is ideal for combining recurring workbooks. By connecting directly to a folder, the query will automatically process and append any new files added to that directory upon refreshing.
Place all the weekly Excel files you want to combine into one consistent local or network folder.
Open a new Excel workbook. Go to the Data tab, click on 'Get Data', select 'From File', and then choose 'From Folder'.
Browse to your designated folder and click Open. In the preview window, click the 'Combine' dropdown and select 'Combine & Transform Data'.
In the Power Query Editor, use the transformation tools to map any inconsistent headers to a standard schema, and apply filters to the columns to isolate records for specific offices as needed.
Click 'Close & Load' to append the results to your master dataset. Whenever new weekly files arrive in the folder, simply click 'Refresh All' on the Data tab to update your master sheet.

Automate File Merging with Office Scripts
If you are using Microsoft 365, Office Scripts provide an alternative way to automate data extraction across standard applications like Outlook and Teams.
Combine Multiple Workbooks Easily in WPS Office
Instead of building complex Power Query flows, WPS Spreadsheet offers a built-in tool to merge multiple Excel files and worksheets with just a few clicks.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet.
- 2. Access the Merge Tool: Navigate to the 'Data' tab and click on the 'Merge Workbooks' or 'Merge Sheet' option in the top ribbon.
- 3. Select Merge Type: Choose 'Merge multiple workbooks into a single workbook' from the dialog box options.
- 4. Add Weekly Files: Click 'Add Files' to select all the weekly Excel reports from your local folder.
- 5. Execute Merge: Click 'Start Merging' to automatically generate your combined master dataset without writing any queries.

Frequently Asked Questions
Do all weekly files need to have the exact same column names for Power Query to work?
Ideally yes, but Power Query can handle missing or inconsistent headers. You will need to add transformation steps in the Power Query Editor to manually rename or map these varying columns to a standard schema before appending them.
How do I update my master dataset when a new weekly Excel file is generated?
Simply place the new weekly file into your designated source folder. Then, open your master dataset in Excel, go to the Data tab, and click 'Refresh All'. Power Query will automatically process the new file and append its data.
Can I filter out unnecessary data from the weekly files before combining them?
Yes. During the 'Combine & Transform Data' phase in Power Query, you can click the drop-down arrows on the column headers to filter out specific records or select targeted offices before loading the final appended dataset into Excel.




