How to Combine Multiple Excel Workbooks into a Summary Using Power Query
Question details
The user wants to combine data from multiple vendor Excel workbooks with identical formats into a single summary workbook that updates automatically.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Consolidating periodic reports from multiple vendors into a master summary file using folder-based imports.
- Observed behavior
- Needs a streamlined method to import all files from a designated folder and refresh the combined data automatically when the source files change.
Ensure all the source workbooks you want to combine have the exact same structure (identical column headers and data layouts) and are stored together in a single, dedicated folder on your computer.
Combine Workbooks Using Power Query 'From Folder' Feature
The most robust and standard way to combine multiple files with the same structure is by connecting Power Query directly to a folder.
By setting up a folder-based query, Excel will automatically append all files placed into that folder. If a vendor submits a new file, you only need to drop it into the folder and hit refresh.
Create a dedicated folder on your drive and place all 30 vendor workbooks inside it. Ensure no other unrelated Excel files are in this folder.
Open a new, blank Excel workbook. Go to the 'Data' tab on the ribbon, select 'Get Data' > 'From File' > 'From Folder'.
Browse to the folder containing your vendor files and click 'Open'.
In the preview window that appears, click the 'Combine' dropdown button at the bottom and select 'Combine & Transform Data'.
Select the sample sheet or table to base the combination on and click 'OK'. The Power Query Editor will open. If the data looks correct, click 'Close & Load' in the top-left corner to generate your summary table.
Use a Parameterized Summary Workbook Template
If you are using a pre-built summary workbook that has a parameter setup for dynamic file paths, you can quickly link it to your target folder.
Easily Combine Multiple Workbooks with WPS Spreadsheet
WPS Spreadsheet features an intuitive 'Merge Workbooks' tool that allows you to consolidate multiple Excel files into a single sheet quickly, without needing to configure complex Power Query rules.
- 1. Open WPS Spreadsheet: Launch WPS Office, open a new Spreadsheet, and navigate to the 'Data' tab on the top ribbon.
- 2. Access the Merge Tool: Click on 'Merge Workbooks' or 'Consolidate' located in the Data tools section.
- 3. Add Vendor Workbooks: Click 'Add Files' in the prompt window and select all the vendor workbooks you wish to combine.
- 4. Execute the Merge: Choose whether to merge them into multiple sheets or a single consolidated summary sheet, then click 'Start Merging'.
Frequently Asked Questions
Do all source workbooks need to have the exact same format for Power Query to combine them?
Yes, for a seamless combination, all workbooks in the folder should have identical column headers, data types, and sheet names. If the formats differ, you will need to apply transformation steps in the Power Query Editor to align the columns before loading the final summary.
How do I update the summary when a new vendor workbook is received?
Simply move the new workbook into the same folder you used for the initial import. Then, open your summary workbook, go to the 'Data' tab, and click 'Refresh All' (or right-click the summary table and click 'Refresh'). Power Query will automatically process and append the new file.
Can I exclude certain files in the folder from being combined into the summary?
Yes. Before clicking 'Combine' in the Power Query preview window, select 'Transform Data'. In the Power Query Editor, you can click the filter dropdowns on the 'Name' or 'Extension' columns to exclude temporary files, specific vendor names, or non-Excel files, and then combine the remaining data.
