Thurlow Provisions runs nineteen depots, and every one of them drops the same file into the same place on the first working day of the month. The folder is \\thu-fs02\sales\monthly. The file is Depot_<name>_2026-08.xlsx: one row per product line, with Date, Depot, Product, Units, Net sales and Margin % across the top and a total row at the bottom.
Until March 2024 somebody opened all nineteen and copy-pasted them under each other. Since then there has been a query: Data → Get Data → From File → From Folder, point it at \\thu-fs02\sales\monthly, Combine & Transform, load to the Data Model, and the board pack's pivot tables sit on top of it. It takes forty seconds to refresh and it has been right every month for two and a half years.
On 3 September 2026 it was refreshed for the August board pack and it reported £1,611,140 of net sales across the nineteen depots. The real figure was £1,623,230.
£12,090 out, 0.74% low — small enough that nobody looked twice at it, and it is not one error. It is two, in opposite directions, netting off:
Depot_Kesgrave_2026-08.xlsxwas in the folder twice, the second copy calledDepot_Kesgrave_2026-08 (1).xlsxafter somebody saved the emailed attachment a second time. Kesgrave's 501 rows were appended twice and its £84,310 was counted twice.Depot_Dunmore_2026-08.xlsxhad been rebuilt by a new assistant with a title row at the top, so the column headers sat on row 2 instead of row 1. All 412 of Dunmore's rows arrived in the combined table with every column empty. Its £96,400 was counted as nothing at all.
£84,310 too much and £96,400 too little. The total was believable because the two mistakes were nearly the same size.
What acted on it was not the board pack. The September replenishment plan for short-life lines is built from the same query, on a three-month average, so Kesgrave was ordered against a demand that had never existed and Dunmore against one that had disappeared. £18,700 of short-life stock was written off at Kesgrave over the first fortnight of September. Dunmore had gaps on 31 lines for nine days and lost £26,400 of sales that it can show from its own till data. On 8 September the Dunmore manager rang to ask why his September order was a third of normal, and that is how anyone found out. The August board pack was restated on 16 September.
The query did exactly what it was told. Combine everything in this folder is three claims — combine, everything, and this folder — and a refreshed query never restates any of them. It gives you one table with no statement attached about what went into it.
1) The Folder as the Query Saw It
This is the provenance table nobody was loading: one row per file the query actually read, with the rows and the money each file contributed to the combined table. Every number in it comes from the August refresh.
Twenty Files, Nineteen Depots
The August refresh, file by file: the rows and the net sales each workbook contributed to the combined table. Kesgrave is in the folder twice and appears twice. Dunmore is in it once and contributed 412 rows with every column empty. The two faults are £84,310 too much and £96,400 too little, which is why the total at the bottom looks like a normal month.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Two rows carry the whole incident. Kesgrave appears twice, with 501 rows and £84,310 each time, because there were two files and the combine has no opinion about that. Dunmore appears once with 412 rows and nothing in them.
Note what the totals do. 8,812 rows is a plausible-looking number next to last month's 8,270. £1,611,140 is a plausible-looking number next to last month's £1,588,400. Nothing in the combined table is out of range, because the two errors were both large and pointed in opposite directions.
🎯 Scenario: Build this table for your own folder query before you read on. In the query editor, right-click the combined query → Reference, then Group By on Source.Name with two aggregations: Count Rows, and Sum of your money column. Load it to a worksheet next to the report. It costs one extra query and it is the only thing in the workbook that knows which files the numbers came from.
2) Folder.Files Includes Subfolders
From Folder generates Folder.Files("\\thu-fs02\sales\monthly"), and Folder.Files is recursive: it returns every file in that folder and in every folder underneath it, to any depth. The Folder Path column is there to tell you so, and it is usually the first column people remove.
This is the cheapest way to double a year. Somebody tidies the share by making \monthly\Archive 2025\ and dragging last year's twelve files into it — a tidy-up that moves nothing out of the query's reach. The next refresh appends 2025 to 2026, and whether that shows up depends entirely on whether the report filters by a real date or by a month name.
The alternative is Folder.Contents, which returns only what is directly in the folder, with subfolders as rows you can ignore. You can switch a query to it by hand. The other way is to keep Folder.Files and filter:
= Table.SelectRows(Source, each [Folder Path] = "\\thu-fs02\sales\monthly\")
One habit is worth more than either: keep Folder Path in the output, and put =COUNTA(UNIQUE(Sales[Folder Path])) next to the report. It should be 1. When somebody tidies the share, it becomes 2 the same day.
🎯 Scenario: Open your own folder query and look at its steps. If the first thing after Source removes columns, click Source and read the Folder Path column. Ask whether you can promise there will never be a subfolder in there — not today, but in eighteen months, after someone who has never heard of this query decides the folder is untidy.
3) The Combine Files Button Writes Four Queries and a Function
Pressing the double-arrow on the Content column is a bigger act than it looks. Excel writes a Helper Queries group containing:
| Query | What it is |
|---|---|
Parameter1 | A parameter typed as binary, standing in for "a file" |
Sample File | The first file in the folder after your filters, as a binary |
Transform Sample File | A normal query with steps you can see and edit, running against the sample |
Transform File | A function: the sample's steps, wrapped so they can be applied to any file |
And in the main query, two steps that do the work:
= Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content]))
= Table.ExpandTableColumn(#"Invoked Custom Function", "Transform File", {"Date", "Depot", "Product", "Units", "Net sales", "Margin %"}, …)
Three consequences follow, and they are the subject of the next three sections.
The sample is one file, and it is the first one. Every rule the transform contains was inferred from a single workbook. Rename a file so that a different one sorts first, and the sample changes.
Transform Sample File is where you edit. Changing the function directly is possible and a bad habit; the sample query exists so you can see what you are doing, and the function follows it.
Filtered Hidden Files1 is Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), and it is the reason the Combine button survives a colleague who has one of the source files open. Excel writes a ~$Depot_Ardleigh_2026-08.xlsx lock file next to it; that file is hidden, and hidden files are gone before the function ever sees them. A hand-rolled Folder.Files + Excel.Workbook query has no such step, which is why hand-rolled folder queries fail on the one morning somebody is still editing their file.
🎯 Scenario: In your own workbook open Queries & Connections, expand Helper Queries, and click Transform Sample File. Read its steps out loud. That list, and nothing else, is what is being applied to all nineteen files.
4) Navigation Happens by Name
The first step of Transform Sample File is almost always this:
Source = Excel.Workbook(Parameter1, null, true),
Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data]
That is a lookup by name. Item="Sales" means the tab called Sales, exactly, case and spaces included. A depot that renames its tab Sales 2026, or sends a workbook where the tab is still Sheet1, produces:
Expression.Error: The key didn't match any rows in the table.
This one is loud, and loud is cheap: the refresh stops, nobody publishes anything, and you spend ten minutes finding the file. The positional alternative takes the first sheet whatever it is called:
Sales_Sheet = Source{0}[Data]
Which of the two you want is a real decision. Item="Sales" refuses to read the wrong tab and stops when it cannot find the right one. Source{0} never stops, and cheerfully reads a Notes tab that somebody dragged to the front. In a folder of files from nineteen different people, the name lookup is usually right, because the failure you can see beats the one you cannot.
There is a third Kind. Excel.Workbook lists sheets, named ranges and Tables, so if every depot's data lives in a Table called tblSales, Source{[Item="tblSales",Kind="Table"]}[Data] is better than either: a Table carries its own headers and its own extent, so no promotion step is needed and rows typed under it are inside it.
🎯 Scenario: Check what your transform navigates to. If it is Kind="Sheet", the tab name is now part of your monthly process, whether or not the people sending you files know that. Send them a template with the tab already named, and say why.
5) Promote Headers Runs Once Per File
The second step is Table.PromoteHeaders, and this is where Dunmore went.
#"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true])
Table.PromoteHeaders promotes the first row of that file, whatever it happens to contain. It is not looking for your headers. It does not compare against the sample. It takes row 1 and makes it the column names.
Dunmore's assistant had rebuilt the file from the July one and typed a title across the top — Dunmore — August 2026 in A1, the headers pushed down to row 2. So for that file, and only that file, the promoted column names came out as Dunmore — August 2026, Column2, Column3 and so on, and the real headers became the first row of data.
Nothing failed. Table.PromoteHeaders cannot fail — every table has a first row.
🎯 Scenario: Ask your transform what it assumes about row 1. If files come from other people, add a step that finds the header row instead of trusting it: filter out rows where your key column is null before promoting, or use Table.Skip with a condition. Even a plain Table.SelectRows that drops rows where every column is null is worth its place.
6) Expand Is a Fixed List of Column Names
Here is why a promoted title row produces nothing instead of an error.
Table.ExpandTableColumn takes an explicit list of column names, captured when you pressed the button:
{"Date", "Depot", "Product", "Units", "Net sales", "Margin %"}
For every nested table it looks for those six names. Dunmore's nested table had six columns too, called Dunmore — August 2026, Column2, Column3, Column4, Column5 and Column6. None of them matched. A column that is asked for and not found expands as null — not as an error, not as a warning, not as a shorter table. Dunmore contributed 412 rows, every cell of them empty, and SUM ignores empty cells as a matter of policy.
The same mechanism, with a smaller cause, is a column somebody renamed: Net sales → Net Sales in one file is a column of nulls for that file. The expand list is case-sensitive, like every other name comparison in M.
And the list is fixed, which cuts the other way too. When all nineteen depots start sending a Promo code column next January, the expand step will keep asking for the same six names and that column will not appear. Nothing will tell you. You re-open the expand step and tick it.
This is also the step that decides whether your folder query fails loudly or quietly, because of where the types get set. When Combine Files writes the transform, it puts a Changed Type step inside the per-file function:
= Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"Net sales", type number}, …})
Naming a column that does not exist in that file raises Expression.Error: The column 'Net sales' of the table wasn't found, and you get an error you cannot miss. Thurlow's analyst had deleted that step in March 2025, for a defensible reason: one depot had sent a file with an empty Margin % column, the typing step failed, and the whole refresh died at 07:40 on a board morning. Moving the type change to after the append fixed that and is advice you will find in a hundred places, including from people who know exactly what they are doing.
It is still the trade. Types inside the function: a file that changed shape fails, visibly, and you fix it. Types after the append: nothing fails, and the file that changed shape becomes a block of nulls in a table of nine thousand rows.
🎯 Scenario: Decide which of the two you want, deliberately, and write it down next to the query. If you choose types after the append, you owe the null check in section 11 — =ROWS(Sales)-COUNT(Sales[Net sales]) — somewhere visible on the report.
7) The Copy With "(1)" In Its Name
Kesgrave's file was in the folder twice because someone opened the email a second time and Windows did what Windows does: Depot_Kesgrave_2026-08 (1).xlsx, saved alongside the first one.
There is no mechanism in Power Query that objects to this. Both files match the extension filter. Both have a Sales tab, headers on row 1, 501 good rows. The combine appended both because both were there, and it was right to.
What makes it expensive is that no total-based check can find it. This is the part worth slowing down for. Every depot's file ends with its own total row — a control total, produced by the depot, independent of your query. So the obvious reconciliation is to keep those total rows in a second query and compare:
=SUM(Sales[Net sales]) - SUM(Control[Depot total])
It found neither. Dunmore's total row went through the same mis-promoted file as its data, so it arrived null in Control exactly as the data arrived null in Sales: both sides lost £96,400 and the difference stayed at zero. And Kesgrave's duplicate file contains a total row of its own, so the duplicate is counted twice in Sales and twice in Control, the two sides move together, and the difference stays at zero again.
A reconciliation that compares the combined table against control totals drawn from the same folder cannot see either fault, because both faults are on both sides of it.
This is the general shape of the thing. Every check drawn from inside the combined table compares the folder with itself. The only check that catches a duplicate file is a check on the folder: how many files, against how many there should be.
=COUNTA(UNIQUE(Sales[Source.Name])) 19 depots, 19 files returned 20
🎯 Scenario: Write down, in a cell, how many files you expect this month. Nineteen depots, nineteen files. Then put the count next to it. Two cells and a subtraction, and it is the one check in this article that has no substitute.
8) Total Rows Inside Each File Append as Data
Each depot's file ends with a total row, and that row has no idea it is special. Excel.Workbook reads the whole used range, so unless something removes it, every one of those nineteen total rows is appended as a data row and the combined table double-counts every depot by exactly 100%.
At Thurlow this was handled on day one, because the error is too big to miss: the August total would have come out at £3.2m. It is only invisible when the total row is partial — a subtotal per product group, a "Returns" line under the data, a note typed two rows below the last line — and then it is a few percent, in the same territory as the rest of this article.
The robust filter is on the key column, not on the word "Total":
= Table.SelectRows(#"Promoted Headers", each [Date] <> null and [Product] <> null)
That survives a depot writing TOTAL, Total for month, or nothing at all in column A, because it is asking whether the row is a transaction, not whether it is labelled as one.
🎯 Scenario: In the query editor, click the combined query, click the filter arrow on your key column and look for (null) in the list. Anything there is a row that is in your table and is not a transaction. While you are there, sort descending on the money column and read the top five rows: a total row that slipped through is always near the top.
9) Source.Name Is the Only Thing That Knows Where a Row Came From
The Combine button keeps the file name and renames it Source.Name, and it is the most valuable column in the table. Everything in this article is diagnosable in one pivot: Source.Name down the rows, count of rows and sum of money as the values.
That pivot would have shown, in August, two Kesgrave lines and a Dunmore line with 412 rows and no money, in about four seconds.
Keep it. Keep Folder Path too, and if the file name carries the month, split it into a proper File month column with Extract → Text Between Delimiters so you can check the month is the one you think you are reporting. A folder query that has dropped Source.Name is a table that cannot answer the question "which file did this come from", and after that, every investigation is opening nineteen workbooks by hand.
🎯 Scenario: Build the provenance pivot now and leave it on a sheet called Check, next to the report. Rows: Source.Name. Values: count of rows, sum of the money column. Refresh it with everything else. A person glancing at nineteen lines will see two called Kesgrave without having to be told what to look for.
10) Refresh, and What "Loaded" Means
The query was right for two and a half years and wrong in August, and it was wrong the instant it refreshed. Three things about refresh are worth stating plainly.
Refresh All is not automatic. Ctrl Alt F5 refreshes every query and every pivot in the workbook. Opening the file refreshes nothing unless the connection has Refresh data when opening the file ticked, in Queries & Connections → right-click → Properties. A folder query in a file nobody has refreshed is showing you the folder as it was on some past date, which is exactly as wrong as the folder being incomplete, and quieter.
Background refresh reorders your morning. With Enable background refresh on, the refresh returns control to you before it has finished, so a pivot read too quickly is read against last month's data. On a report you are about to send, turn it off.
A connection-only query has no output to check. If the combined query loads straight into the Data Model and only pivots consume it, there is no table anywhere to run =ROWS() against. That is a fine architecture and it needs the checks in the next section loaded as their own small queries, or as measures, rather than as formulas beside a table that does not exist.
One more, because it wastes a whole morning when it happens: if the folder path comes from a cell rather than being typed into the query, you will meet Formula.Firewall: Query 'X' references other queries or steps, so it may not directly access a data source. It is a privacy-level rule, not a bug. Data → Get Data → Query Options → Privacy → Ignore the Privacy Levels for that workbook, or restructure so the path is a parameter.
🎯 Scenario: Open Queries & Connections, right-click your folder query, Properties, and read the two tick boxes. Then look at the bottom of the query pane at the row count and the timestamp: "8,812 rows loaded" and a date. Both of those are facts about the last refresh, and the row count is one of the numbers you should be able to predict before you read it.
11) Nineteen Files, Nineteen Depots: the Checks That Would Have Caught It
Each of these is one cell, against the combined table loaded as Sales. Run them after every refresh, on the sheet the report lives on.
| Check | What it should return | What it returned in August |
|---|---|---|
=COUNTA(UNIQUE(Sales[Source.Name])) | 19 — one file per depot | 20 |
=COUNTA(UNIQUE(Sales[Depot])) | 19 — every depot named in its own rows | 18 |
=ROWS(Sales)-COUNT(Sales[Net sales]) | 0 — no row without a number | 412 |
=ROWS(Sales) | within a few per cent of last month's 8,270 | 8,812 — 6.6% up, on flat sales |
=COUNTA(UNIQUE(Sales[Folder Path])) | 1 — one folder, no subfolders | 1 |
=SUMPRODUCT(--ISNUMBER(SEARCH("(",Sales[Source.Name]))) | 0 — no file name carrying a copy marker | 501 |
=SUM(Sales[Net sales])-SUM(Control[Depot total]) | 0 — the table against the files' own totals | 0 |
Three notes on that table, and they matter more than the formulas.
The first check is the only one that catches the duplicate. Everything drawn from the money — including the last row, which looks like the most rigorous check in the list — compares the folder against itself, and a file that is in the folder twice is in both sides of that comparison twice.
The last row is there to make that visible. It returned zero, all month, on a table that was £12,090 wrong. It is not a bad check; it catches transcription, truncation and a file that failed to load. It cannot catch this, and a check that returns zero for the wrong reason is worse than no check, because of what people conclude from it.
The fifth is in the list because of June, not August. Somebody made \monthly\Archive 2025\ on 12 June and the next refresh pulled twelve extra files. That one was found the same day — the total went up by 68% — and it is the reason Folder Path is still in the output.
12) Twelve Traps
Folder.Filesis recursive. A subfolder created inside your source folder is inside your query.Folder.Contentsis the non-recursive one.- The sample file is the first file, so every rule in the transform was inferred from one workbook that happened to sort first. Rename the files and the sample changes.
Item="Sales",Kind="Sheet"is an exact, case-sensitive name lookup. A renamed tab stops the refresh.Source{0}[Data]takes the first sheet instead and never stops, including when the first sheet is the wrong one.Table.PromoteHeaderspromotes row 1, whatever is in it. One title row above the headers and that file's columns are named after the title.- Expanding a column that a file does not have gives null, not an error. A renamed or mis-promoted column is a block of empty rows, and
SUMskips empty cells without comment. - The expand list is fixed at the moment you press the button. A column every file starts sending next year will not appear until you edit the step.
- Types inside the per-file function fail loudly; types after the append fail silently. Both are defensible. Only one of them needs the null check.
- Extension filters are case-sensitive in M.
[Extension] = ".xlsx"does not match.XLSX. UseText.Lower([Extension]). - Total rows inside each file append as data. Filter on a key column being non-null, not on the word "Total", which every depot spells differently.
~$lock files are hidden, so the Combine button'sFiltered Hidden Filesstep removes them and a hand-writtenFolder.Filesquery does not.- A duplicate file is invisible to every money-based reconciliation, because the control totals are duplicated along with the data. Count the files.
- Nothing refreshes on open unless you tick it, and with background refresh on, a pivot read straight after a refresh may be read before it finished.
The lesson Thurlow took out of August was not about Power Query, and it was certainly not that the query had been a mistake — nineteen files by hand was worse, and wrong more often.
It was that a combined table has no provenance. Nine thousand rows arrive looking identical, and which file each row came from, how many files there were, and which of them were read the way you meant them to be read are questions the table does not answer unless you make it. The fix is not a better transform. It is four cells and a pivot on Source.Name, refreshed with everything else, saying how many files there were and how many rows and pounds each one brought — so that the day somebody saves an attachment twice, or types a title above the headers, the file that changed is the one you are looking at.