Power Query Problems

How to Automate Combining Weekly Excel Files with Power Query

Huda QurayshiHuda Qurayshi Nov 2, 2026 869 views

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.

How to Automate Combining Weekly Excel Files with Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Organize your files

Place all the weekly Excel files you want to combine into one consistent local or network folder.

2
Connect to the folder

Open a new Excel workbook. Go to the Data tab, click on 'Get Data', select 'From File', and then choose 'From Folder'.

3
Combine and Transform

Browse to your designated folder and click Open. In the preview window, click the 'Combine' dropdown and select 'Combine & Transform Data'.

4
Map columns and filter 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.

5
Load and refresh

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.

Use Power Query's From Folder Connector
Automation Setup: Once the initial query is built and formatted, you never have to map the columns again. Just drop new files into the folder and refresh.
WPS Spreadsheet Data Tools

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. 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet.
  2. 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. 3. Select Merge Type: Choose 'Merge multiple workbooks into a single workbook' from the dialog box options.
  4. 4. Add Weekly Files: Click 'Add Files' to select all the weekly Excel reports from your local folder.
  5. 5. Execute Merge: Click 'Start Merging' to automatically generate your combined master dataset without writing any queries.
Merge multiple weekly workbooks into a single sheet instantly.Fully compatible with Microsoft Excel (.xlsx, .xls) formats.No coding or advanced query knowledge required.Free, lightweight, and user-friendly interface.
microsoft office alternative - wps office

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.