Guide Power Query
I have forty files that all need the same treatment
One file per branch, or per month, or per supplier, all the same shape. Opening each one and copying it into a master sheet is an afternoon that comes round again next month.
Use Data → Get Data → From File → From Folder, point it at the folder, and choose Combine & Transform. Power Query reads every file, applies the same steps to each, and stacks them into one table. Add a file to the folder next month and press Refresh — it is included with no further work. Add a column for the file name so you can always tell which row came from where.
Why copy and paste does not survive
Copying forty files into one sheet works exactly once. It has three problems, and they all arrive later:
- It has to be redone every time a file changes or a new one appears.
- Nothing records where a row came from. When a number looks wrong, you cannot trace it back to its file.
- A file with columns in a different order lands in the wrong columns, and pasting does not notice.
A folder query fixes all three. It matches columns by name rather than by position, it can add the file name automatically, and it re-runs on a button.
| What you see | What actually happened | What fixes it |
|---|---|---|
| An afternoon of copy and paste, monthly | The combining is manual | Point a query at the folder instead |
| Cannot tell which file a row came from | The source is lost when you paste | Keep the Source.Name column |
| Columns landed in the wrong place | One file had a different column order | Power Query matches on name, not position |
| One bad file breaks everything | A different shape, or a stray heading row | Fix it in the sample file query |
| Refresh is very slow | Every file is re-read each time | Narrow the folder, or filter before combining |
How to combine a folder of files
Point Power Query at the folder
Data → Get Data → From File → From Folder. Browse to the folder and click Open. A preview lists every file it found, with its name, extension and date — not the contents yet, just the list.
Filter out anything you do not want
Click Transform Data rather than Combine, so you can tidy the list
first. Use the arrow on Extension to keep only .xlsx, and filter the
Name column to exclude files starting with ~$ — those are Excel's
temporary files for anything currently open, and they will break the refresh if included.
Combine, using the first file as the pattern
Click the double-arrow icon at the top of the Content column. Power Query opens one file as a sample and asks which sheet or table to take from each. Pick it and click OK.
It then builds a small set of helper queries and applies the same extraction to every file.
Clean once, in the sample file query
This is the part worth understanding. In the Queries pane there is a query called Transform Sample File. Steps you add there — removing a heading row, setting a column to Text, trimming spaces — run against every file.
Clean the sample once and all forty files are cleaned. That is the whole return on the exercise.
Keep the file name column
The combined table includes Source.Name. Keep it. When a total looks wrong, that column is the difference between finding the bad file in seconds and opening forty files by hand.
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
Adding a file next month
Save it into the folder and press Data → Refresh All. It is picked up because the query points at the folder, not at a list of files. Nothing else to do.
When one file breaks the refresh
Usually a file with a different sheet name, a merged cell, or an extra heading row. The error message names the file. Two options: fix the file, or add a step to the sample query that copes with both shapes — for example, removing rows until the real heading appears rather than removing a fixed number.
CSV folders work the same way and are faster
The same feature reads a folder of CSVs, and it is considerably quicker because there is no workbook to open. If you control the export, CSV is the better choice for this — just set your ID columns to Text in the sample query, or you will lose leading zeros across every file at once.
Keep the folder clean
The query reads everything you have not filtered out. A folder that also holds last year's archive, a copy called final v2 and somebody's notes will produce a total that is wrong in a way nobody can see. One folder, one purpose.
Questions people ask
How do I combine several Excel files into one?
Use Data > Get Data > From File > From Folder, point it at the folder, and choose Combine & Transform. Power Query stacks every file into one table and can re-run on a button.
Will new files be included automatically?
Yes. The query points at the folder rather than a list of files, so anything you save there is picked up on the next refresh.
How do I know which file a row came from?
Power Query adds a Source.Name column. Keep it — it is how you trace an odd number back to the file that produced it.
What if the files have columns in a different order?
Power Query matches columns by name, not position, so a different order is handled. A different column name is not, and shows up as a new column of nulls.
Why does my refresh fail with a strange file name?
Usually a temporary file. Excel creates files beginning with ~$ for any workbook that is open. Filter them out in the Name column.
Is combining CSVs faster than Excel files?
Yes, considerably — there is no workbook to open. Set your ID columns to Text in the sample query so you do not lose leading zeros across every file at once.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.