Power Query Combine Files errors and how to fix each one
By the Analistable team · Updated · 5 min read
Almost every Combine Files error comes from the sample file. Power Query records your steps against one file, then replays them on every other file. When another file has a renamed column, a different sheet name, a stray text value or a lock file, the replay fails. Find the failing file with Keep Errors, then make the sample transform tolerant: navigate by position, select columns with MissingField.UseNull, and filter out ~$ files.
Part of our guide: How to merge data in Power BI, Power Query and Tableau
How Combine Files builds its queries
When you choose Get Data → From Folder → Combine & Transform, Power Query creates a helper group: a Sample File parameter (usually the first file in the folder), a Transform Sample File query where your edits go, and a Transform File function generated from it. The main query lists the folder, calls Transform File on every row, then expands the results into one table using the column names it saw in the sample.
That last detail explains most errors. Anything hard-coded against the sample (a sheet name, a column list, a type for each column) becomes a rule every other file must obey. The basics of the folder combine are covered in combining files from a folder; this page is about what to do when it breaks.
Find the file that fails first
Errors often show only as a yellow warning bar or an error count after loading. Before fixing anything, find the culprit:
- Open the main combine query and select the step just after Invoked Custom Function (where the Transform File column appears).
- Right-click the Transform File column and choose Keep Errors. The rows left are the files that failed.
- Click the white space beside an Error cell to read the full message, and note the file name in the Name column.
- Delete the Keep Errors step when you are done. It is a diagnostic, not a fix.
| Name | Transform File | Message |
|---|---|---|
| Sales_2026-03.xlsx | Error | The key didn't match any rows in the table. |
| ~$Sales_2026-05.xlsx | Error | External table is not in the expected format. |
Four files combine and two fail, so the loaded table quietly has four months instead of six. Always compare the file count with what you expect.
Expression.Error: The column 'X' of the table wasn't found
This means a step refers to a column by name and one file does not have it. The usual culprit is the automatic Changed Type step, either in Transform Sample File or at the end of the main query, which lists every column from the sample with a type. If March calls it “Net Sales” and April calls it “Net sales”, April fails, because Power Query column names are case-sensitive.
- Delete the auto Changed Type step in the main query and set types once, after any renaming, on only the columns you need.
- Rename to a standard in Transform Sample File before typing, so every file arrives with the same names.
- Select columns tolerantly:
Table.SelectColumns(Source, {"Date", "Region", "Net Sales"}, MissingField.UseNull)returns a null column instead of an error when a file lacks one.
// Transform Sample File: normalise headers, then keep known columns
let
Source = Excel.Workbook(Parameter1, null, true),
Data = Source{[Item="Sales", Kind="Sheet"]}[Data],
Promoted = Table.PromoteHeaders(Data, [PromoteAllScalars=true]),
Clean = Table.TransformColumnNames(Promoted, each Text.Proper(Text.Trim(_))),
Kept = Table.SelectColumns(Clean, {"Date", "Region", "Net Sales"}, MissingField.UseNull)
in
KeptA related, quieter problem: columns that exist in later files but not in the sample are dropped, because the expand step only lists the sample's columns. No error appears. If files gain columns over time, see combining files with different columns.
DataFormat.Error: We couldn't convert to Number
A type step met a value it cannot convert, such as “n/a”, a dash or a number stored with a different decimal separator. The whole cell becomes an error, and if you later use Remove Errors, the entire row disappears.
- Use Keep Errors on the column to see which values fail, then fix them upstream or with Replace Values before the type step.
- For “1.234,56” style numbers, set the type with Change Type → Using Locale and pick the source locale.
- Prefer Replace Errors with null over Remove Errors, so the row survives and you can count the nulls.
The key didn't match any rows in the table
The Transform Sample File navigation step picks a sheet by name, for example Source{[Item="Sales",Kind="Sheet"]}. A file whose sheet is called “Sheet1” or “Sales ” (with a trailing space) has no matching row. Either standardise the sheet names or navigate by position; both approaches are explained in combining files with different sheet names.
Files that are skipped or fail for no clear reason
| Symptom | Cause | Fix |
|---|---|---|
| Error: external table is not in the expected format | Excel lock file (~$Name.xlsx) from an open workbook | Filter Name with “does not begin with” ~$ |
| Some workbooks missing | Extension filter is .xlsx but files are .XLSX or .xlsm | Filter on Text.Lower([Extension]) |
| Extra or duplicated rows | Folder.Files also reads subfolders, such as an Archive folder | Filter on Folder Path |
| Fewer files than expected after load | A Remove Errors step dropped failing files | Replace it with Keep Errors while debugging |
If the folder contains CSV and Excel files together, the transform written for one format fails on the other. Keep one format per folder, or filter on Extension before the combine.
Make the combine robust next time
- Pick a representative sample file: right-click a file in the folder query and choose to use it as the sample if the first one is untypical.
- Do cleaning in Transform Sample File, and keep the main query to filtering files, invoking and expanding.
- Add a source column (the file name is already there as Source.Name) so you can count rows per file after each refresh.
- Keep a small check: a pivot of rows by Source.Name will show at once when a month vanishes.
If you only need to analyse the combined data rather than maintain a refreshable query, Analistable takes a different route: you drop the files in, each becomes a table in DuckDB inside your browser, and you ask questions in plain English. Files are not uploaded. The trade-offs are covered in Power Query vs Analistable, and the wider options sit in the Power BI, Power Query and Tableau guide.
Frequently asked questions
- Why does Combine Files work in the preview but fail on refresh?
- The preview only evaluates the first rows of a few files. A full refresh runs every file, so a later file with a different column name, sheet name or bad value only fails then.
- How do I see which file caused the error?
- In the main query, select the step after Invoked Custom Function, right-click the Transform File column and choose Keep Errors. The Name column shows the failing files.
- Should I use Remove Errors to make the error go away?
- Only once you know why rows fail. Remove Errors deletes whole rows or whole files, so totals become wrong without any warning.
- Can I change which file is the sample?
- Yes. The Sample File parameter points at one row of the folder list. Change it, or right-click a file in the folder preview and use it as the sample, so the transform is built on a typical file.