How to Combine Excel Reports with Different Column Names using Power Query
Question details
The user needs to combine weekly Excel reports that have varying column names without causing header promotion errors in Power Query.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Combining multiple weekly reports containing varying names, dates, and shifts into a single consolidated dataset.
- Observed behavior
- Promoting names to column headers causes errors during the combination process because the column names differ across the source files.
Ensure all the weekly Excel reports you want to combine are placed in a single, dedicated folder and close any files that are currently open to avoid locking errors during the query.
Use Folder-Based Power Query and Defer Header Promotion
By importing files from a folder and delaying the promotion of column headers until after the files are appended, you can successfully combine reports with varying column names.
When you combine files, Power Query often automatically promotes the first row to headers and hardcodes those specific names in the 'Changed Type' step. If subsequent files have different names, the query breaks.
To resolve this, you must standardize the source structure, remove the automatic header promotion on the individual files, append the raw data, and only transform the consolidated dataset.
Navigate to the Data tab in Excel, select 'Get Data' > 'From File' > 'From Folder', choose the directory containing your weekly Excel reports, and click Open.
In the preview window, click 'Transform Data' instead of Combine. This opens the Power Query Editor displaying the list of files in the folder.
Click the 'Combine Files' button (the two downward arrows icon) located in the header of the 'Content' column. Select the relevant sheet containing your data and click OK.
Look at the 'Applied Steps' pane on the right side of the screen. Delete the 'Promoted Headers' and 'Changed Type' steps so that your columns remain generically named (e.g., Column1, Column2).
With all file data now appended generically, apply filters to remove blank rows or repeated file headers. Finally, unpivot or rename the columns to standardize the names, dates, and shifts before loading the data into your workbook.
Looking for a Lighter Way to Manage Spreadsheets?
If dealing with complex Power Query transformations and header errors in Microsoft Excel is slowing you down, try WPS Office. It offers a lightweight, highly compatible spreadsheet tool with built-in features to merge and manage data effortlessly.
- 1. Download WPS Office: Visit the official WPS website and download the free suite for your operating system.
- 2. Open Your Excel Files: Launch WPS Spreadsheet and open your existing .xlsx or .csv report files with full format compatibility.
- 3. Use Smart Merge Tools: Navigate to the Data tab to utilize built-in consolidation tools that simplify combining multiple reports.
Frequently Asked Questions
Why does Power Query give a 'Column of the table wasn't found' error?
This happens when a step in your query (like Promoted Headers or Changed Type) explicitly references a column name that exists in your sample file but is missing or spelled differently in another file being combined.
Can I combine Excel files with different column orders?
Yes. If the column names are identical but in a different order, Power Query will automatically align them by column header name when you append the tables, provided you promote headers before appending.
How do I stop Power Query from automatically changing data types?
You can disable automatic data type detection by going to File > Options and settings > Query Options > Data Load, and unchecking 'Automatically detect column types and headers for unstructured sources'.
Is it possible to combine specific sheets from multiple workbooks?
Yes. When extracting file contents in the Power Query Editor, you can filter the 'Item' or 'Name' column to only include specific sheet names before expanding the data.
