Combine Excel Workbooks by Column Headers with Power Query
Question details
Combine data from 75 to 100 Excel workbooks into a master table using standardized column headers, even when column positions differ.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A master workbook needs to collect rows from user-created workbooks with varying column order but consistent headings such as PO#.
- Observed behavior
- The desired master table aligns values under the correct headers and can be refreshed when source workbooks change.
Place the source workbooks in a dedicated folder, separate from the master workbook. Check that header spelling is consistent and that the data is available in the same named sheet or Excel table in each file.
Combine folder workbooks by header name in Power Query
Use Power Query to append rows from multiple workbooks while matching columns by their standardized names rather than their positions.
Column order can vary, but the combine process must be able to find the same sheet or table in each workbook. The sample file also determines which columns are initially expanded into the master query.
In the master workbook, select Data > Get Data > From File > From Folder, browse to the source folder, and select Transform Data.
In Power Query Editor, use the Extension and Name filters to keep the source Excel files and exclude temporary files whose names begin with ~$.
Select Combine Files on the Content column. Choose a representative sample file and the sheet or table containing the source data, then confirm.
In the resulting query, check that the first data row is not being used as a header. If needed, select Home > Use First Row as Headers in the sample transformation. Use Home > Choose Columns to select standardized names such as PO#, then reorder or rename the columns as needed.
Select Home > Close & Load to place the combined results in Excel. After adding or changing source files in the folder, select Data > Refresh All.

Review and edit Excel workbooks with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks to review headers and edit data as you move between Office tools; use Microsoft Excel Power Query for the folder-combine workflow described above.

Frequently Asked Questions
Will Power Query match columns if they are in a different order?
Yes, the combined query can align columns by header name rather than position. Check that each workbook uses the same header spelling and that the combine transformation promotes the correct header row.
What happens if a workbook is missing the PO# column?
A missing column can produce an error in a transformation that expects it. Open Data > Queries & Connections, edit the query, and inspect the error; then correct the source header or adjust the transformation to handle missing columns.
How do I include newly added workbooks?
Save the new workbooks in the source folder using the same sheet or table name and standardized headers, then select Data > Refresh All in the master workbook.
Why is a new source column missing from the master table?
The combined query initially expands columns based on the sample file. Open the query from Data > Queries & Connections and update the column expansion or selection to include the new header.




