4.2 Power Query: cleaning without retyping
You can turn a genuinely messy export into a clean table, repeatably.
Before:01. Excel — First StepsUnlocks:06. Excel — Automation
Power Query is a separate editor inside Excel where cleaning steps — promote headers, fix types, split, trim, replace — are recorded as a list that replays against next month's file with zero rework. It anchors the cleaning module because it changes cleaning from repeated labour into a written recipe. The mental shift, and the usual confusion, is that the query never edits your source: it reads the source, applies the steps, and lands a result, so undoing a mistake means deleting a step rather than pressing undo.
Work through these
The query editor and the applied-steps list
The editor records every change you make as a step in an ordered list. That list is the recipe that will be replayed on next month's file. Reading the list is how you understand a query somebody else wrote.
Power Query for Excel help · DocsPromote headers, change types, remove rows and columns
Promoting the first row to headers, setting column types, and removing what you do not need are the first steps of nearly every clean-up. Setting types early prevents surprises later.
Split, trim, replace, fill down
Splitting columns, trimming spaces, replacing values and filling blanks downward are the repairs that messy exports need most often. Each is a click, and each was previously an afternoon.
Refreshing next month's file with zero rework
The payoff is that next month's file goes through the same steps automatically. This is what separates a cleaning tool from cleaning by hand.
Practice file
- xl-06-messy-data.xlsx
A deliberately horrible export: merged headers, blank rows, mixed date formats, stray text in number columns. The Power Query exercise.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.