Combine files whose columns differ
Combine files with drifting schemas without losing columns or rows.
~11 min read · advanced · Module 15: Combining files and parameters
By Tahir Nas · Examples run in our simulator · Updated
Before this: Combine files from a folder, Control the appended schema
Real folders drift. September's export added a Channel column; October's renamed OrderID to "Order ID". A combine that worked in August now drops a column and blanks out a month of IDs, without a single error.
A fixed expand list hides the drift
Input folder MonthlyFiles (3 files)
2026-08.csv
OrderID,Qty
A-1,2
2026-09.csv
OrderID,Qty,Channel
B-1,4,Web
2026-10.csv
Order ID,Qty
C-1,1
let
Parsed = Table.AddColumn(MonthlyFiles, "Data", each Table.PromoteHeaders(Csv.Document([Content]))),
Expanded = Table.ExpandTableColumn(Table.SelectColumns(Parsed, {"Name", "Data"}), "Data", {"OrderID", "Qty"})
in
ExpandedResult
| Name | OrderID | Qty |
|---|---|---|
| 2026-08.csv | A-1 | 2 |
| 2026-09.csv | B-1 | 4 |
| 2026-10.csv | null | 1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The expand list was written from the first file. Columns that appear later aren't in it, and a renamed column becomes null. Table.ExpandTableColumn doesn't warn about either.
Normalise names per file, then combine the union
Fix names inside the per-file step, before combining, and use Table.Combine on the parsed tables so every column that exists is kept:
Input folder MonthlyFiles (3 files)
2026-08.csv
OrderID,Qty
A-1,2
2026-09.csv
OrderID,Qty,Channel
B-1,4,Web
2026-10.csv
Order ID,Qty
C-1,1
let
Normalise = (t as table) as table => Table.TransformColumnNames(t, each Text.Remove(_, " ")),
Parsed = Table.AddColumn(MonthlyFiles, "Data", each Table.AddColumn(Normalise(Table.PromoteHeaders(Csv.Document([Content]))), "File", (r) => [Name], type text)),
Combined = Table.Combine(Parsed[Data])
in
CombinedResult
| OrderID | Qty | File | Channel |
|---|---|---|---|
| A-1 | 2 | 2026-08.csv | null |
| B-1 | 4 | 2026-09.csv | Web |
| C-1 | 1 | 2026-10.csv | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
(r) => [Name] inside the outer each: the inner function ignores its row argument and returns the file's Name from the outer row, so every parsed row is tagged with its file.
Decide the contract
- If new columns should flow through, combine the union as above.
- If the output layout is fixed, pass a column list to Table.Combine and add a check that required columns exist in every file.
- If a rename is a one-off, map it explicitly (Table.RenameColumns with MissingField.Ignore) rather than with a broad rule that might merge unrelated columns.
Where to find this in Excel
- Excel for Windows
- Queries pane > Helper queries > Transform Sample File
- What you see
- Fix column names in the example query so every file is renamed the same way before the combine.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Combine files overview (Microsoft Learn).
Common mistakes
- Expanding with the column list generated from the first file and never revisiting it.
- Discovering a renamed column months later because it silently became null.
- Applying a broad renaming rule without checking which columns it merges.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: the Combine Files helper expands with a fixed column list taken from the sample file. Edit that step, or switch to Table.Combine over the transformed tables, when files drift.
- Comparing
Table.ColumnNamesof each file in a check query (one row per file) is a quick way to see drift before combining. - The simulator supports Table.TransformColumnNames and Table.Combine over a list of parsed fixture files, as shown.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Combining files quiz