Power Query Combine Files Problems: Changed Headers, Missing Columns and Schema Drift

Power Query’s folder combine is one of the most useful features in Excel. Point it at a folder, and every file becomes one table that refreshes with a click. It works best when every file has the same structure. When the files start to change, so does the refresh: errors, missing columns, or a table that loads without complaint but is quietly wrong.

How Combine Files works

When you choose Data › Get Data › From File › From Folder and then Combine & Transform, Power Query creates several queries:

• Sample File: one file from the folder, the first one by default.
• Transform Sample File: the steps you build on that example, such as choosing the sheet, promoting headers and removing rows.
• Transform File: a function generated from those steps and applied to every file.
• The main query: calls the function for each file, then expands the results into one table using the column names of the example file, and usually adds an automatic Changed Type step.

That design is the strength and the weakness. One set of steps applies to every file, which is exactly right when the files share a structure. Microsoft’s own guidance for combining files assumes they do. Anything that differs from the example file is where problems start: this is spreadsheet schema drift meeting a process built on one file’s shape.

Problems, symptoms and fixes

Fix 1: rename columns to standard names before combining

Add a rename step in Transform Sample File so every file is standardised before the results are combined:

Renamed = Table.RenameColumns(Promoted,
    {{"Client", "Customer"}, {"Sales", "Revenue"}, {"Territory", "Region"}},
    MissingField.Ignore)

MissingField.Ignore means files that don’t contain an old name are left alone. Keep the list as a deliberate record of which headings you have decided are the same field. Be careful with names that look alike but measure different things: Revenue and Net Sales may not be interchangeable.

Fix 2: combine with every file’s columns, not just the example’s

The generated expand step lists the example file’s column names explicitly. Replace the hard-coded list with the union of all files’ columns:

Expanded = Table.ExpandTableColumn(Invoked, "Transform File",
    List.Union(List.Transform(Invoked[#"Transform File"], Table.ColumnNames)))

Or skip the expand step and combine the per-file tables directly. Table.Combine lines tables up by column name, keeps every column and fills gaps with null:

Combined = Table.Combine(Invoked[#"Transform File"])

With Table.Combine you lose the file-name column the expand step carried, so add a Source File column inside the per-file transform if you need it.

Fix 3: remove or rewrite the automatic Changed Type step

The Changed Type step Power Query adds after combining names every column it saw in the example file. If any file lacks one of them, the refresh fails. Delete it, then set types yourself only for the columns you know every file has, after renaming.

Fix 4: navigate to the sheet by position

The generated step usually looks like Source{[Item="Sheet1",Kind="Sheet"]}[Data]. If another file calls its sheet “October”, that lookup fails. Taking the first sheet avoids the name:

Sheets = Table.SelectRows(Source, each [Kind] = "Sheet"),
Data   = Sheets{0}[Data]

Fix 5: find the header row by content

A fixed Remove Top Rows step only works if every file has exactly the same number of title rows. If they vary, locate the row containing a known heading and skip what is above it. Merging Excel files when the header row is in a different place shows a reusable function.

When fixing the query is the right answer

Stay with Power Query, and invest in making the query robust, when:

• the files come from one system or one template that changes rarely;
• you refresh the same report every week or month;
• you also need to reshape the data: unpivot, join, filter or add calculated columns;
• someone on the team is comfortable maintaining M code.

In that situation a well-built query is hard to beat. It is already in Excel, it refreshes with one click and it documents every step.

When a consolidation workflow may be simpler

The fixes above handle known changes. They are harder to maintain when the changes are new each time: files from many different people or suppliers, templates that drift every month, headings that move, and nobody who wants to own the query. Each new variation means another edit to the rename list, the sheet logic or the header detection, and a failed refresh is often how you find out.

That is the situation ConsoliSheet is designed for. Upload the files as they arrived: it looks for the table in each one, lines columns up by heading rather than position, matches headings that differ only in capitals, spacing, underscores or hyphens, keeps columns that only some files have, shows anything it can’t decide safely, and produces one XLSX with a Source File column. You can then load that clean workbook into Power Query or a PivotTable for the reporting itself. Power Query vs ConsoliSheet compares the two directly.

Common questions