Power Query: combine twelve monthly files in one click

Somewhere in your organisation there is a person whose monthly ritual is this: open twelve files called things like Sales_Jan_FINAL_v2, copy each one, paste it into a master sheet, fix the headers that came along for the ride, and pray nobody sends a thirteenth file called Jan_REVISED.

Power Query exists to delete that ritual. It’s been hiding inside Excel since 2016 — Data → Get & Transform — and it does one thing supremely well: fetch data from somewhere, clean it the same way every time, and land it in your workbook as a refreshable table.

The key idea is worth sitting with for a second. Power Query doesn’t edit your data — it records a recipe. Every step you take is written down, in order, and replayed from scratch on every refresh. The 4:45pm cleaning routine is a checklist you run by hand; Power Query is the same checklist that runs itself.

The folder trick

The headline act. Put the twelve monthly exports in one folder, then:

Data → Get Data → From File → From Folder — pick the folder.
Click Combine & Transform Data. Excel asks which sheet or table to read from each file, using the first file as the sample.
The Power Query editor opens with every file already stacked into one table — plus a Source.Name column telling you which file each row came from. (Keep it: it’s your month column, and your audit trail.)
Do your cleaning once, in the editor — remove the junk rows, fix the types, rename the columns.
Close & Load. One table, all twelve months.

Now the part that changes the job. Next month, file thirteen lands in the folder. You open the workbook and press Refresh (Ctrl+Alt+F5). That’s it. The recipe re-reads the folder, finds thirteen files, replays every cleaning step, and the master table — and every PivotTable and chart built on it — updates. The ritual is now a keystroke.

Files in, recipe replayed, one clean table out. The recipe is recorded once and runs identically forever — that's the whole product.

Reading the editor without fear

The Power Query editor looks like a new application, and technically it is. You only need three landmarks:

Applied Steps (right-hand panel) — the recipe itself. Every click adds a step; click any step to see the data as it looked at that moment; delete a step to un-decide it. It’s undo history that never expires.
The formula bar shows each step in M, Power Query’s language. You don’t need to write M — but seeing that each step is a formula demystifies the whole thing.
Data types — the little icon on each column header. Get these right (123 number, calendar date, ABC text) and half the classic import problems — the text-numbers, the costume dates — are fixed at the door, permanently.

The habits that keep queries healthy are the spreadsheet habits you already have: rename steps so the recipe reads like instructions to a colleague, remove columns you don’t need early, and let the query land in a proper Table.

What belongs in Power Query (and what doesn’t)

A useful division of labour for the modern workbook:

Job	Tool
Fetching and combining files	Power Query
Cleaning that’s the same every month	Power Query
Judgement calls, one-off fixes	You, in the sheet
Analysis and reporting	PivotTables, formulas, charts

If you catch yourself doing the same transformation two months running, that’s the signal: it belongs in the recipe. If a fix needs a human decision, it doesn’t.

Power Query sits in stage six of the path — not because it’s hard (it’s genuinely friendlier than nested formulas) but because its payoff compounds when the downstream layers are already solid. It is also, quietly, the best career move on the whole path: the person who turns the monthly ritual into a Refresh button is the person the office starts calling the Excel person.

Twelve files, one click. The thirteenth file is someone else’s problem now — specifically, the recipe’s.