Power Query Problems

Combine Excel Workbooks by Column Headers with Power Query

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Combine data from 75 to 100 Excel workbooks into a master table using standardized column headers, even when column positions differ.

Combine Excel Workbooks by Column Headers with Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Connect to the folder

In the master workbook, select Data > Get Data > From File > From Folder, browse to the source folder, and select Transform Data.

2
Filter the file list

In Power Query Editor, use the Extension and Name filters to keep the source Excel files and exclude temporary files whose names begin with ~$.

3
Combine the workbooks

Select Combine Files on the Content column. Choose a representative sample file and the sheet or table containing the source data, then confirm.

4
Check headers and columns

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.

5
Load and refresh the master table

Select Home > Close & Load to place the combined results in Excel. After adding or changing source files in the folder, select Data > Refresh All.

Combine folder workbooks by header name in Power Query
Check missing or new columns: If a file lacks a required header, inspect the query for errors and correct the source or adjust the transformation. Newly added headers may require updating the expanded column selection; they do not necessarily appear automatically.
Free Microsoft Office alternative

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.

Review and standardize source workbook headers in WPS SpreadsheetOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse a free, lightweight Office alternative
microsoft office alternative - wps office

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.