Automating Data Cleaning with Power Query in Excel
Manual cleaning — Text to Columns, Remove Duplicates, fixing text-dates — works, but you’d have to repeat every step by hand on next month’s export. Power Query records those steps once, as a reusable pipeline, so next month is a single click.
Step 1: open the Power Query Editor
Data tab → Get Data → From File → From Workbook, pointed at a raw export. This opens the Power Query Editor — a separate window where every action taken is recorded as a named step in the Applied Steps panel on the right.
Step 2: rebuild the cleaning steps, recorded this time
The same three cleaning operations anyone does manually, but each one now becomes a permanent, repeatable step instead of a one-off action:
- —Remove duplicates — Home tab in the Query Editor, Remove Rows, Remove Duplicates.
- —Split a combined column — select the column, Split Column, By Delimiter, choose the separator (e.g. comma for a combined City-State field).
- —Fix a text-formatted date — select the date column, right-click, Change Type → Date. Power Query converts and remembers the transformation.
Step 3: Close & Load
Home tab, Close & Load — the cleaned data loads into the workbook as a Table. This is the step that’s easiest to forget: without it, the query is built but never actually available in the workbook.
Step 4: the payoff — Refresh
Next month, when a new raw export arrives with the same structure, none of the above gets repeated. Update the source file, right-click the query in the Queries pane, Refresh — every recorded step reapplies automatically to the new data in seconds.
One more piece: Append Queries
If monthly exports arrive as separate files that need combining into one running dataset: Home tab, Append Queries, choose the files — Power Query stacks them into one table, and this too becomes part of the repeatable pipeline.
A worked example
A business receiving a raw sales export every month in the same format, with the cleaning pipeline built once in Power Query, turns the monthly workflow into: drop the new file in the same folder, click Refresh, done — a task that used to take 20 minutes of manual cleaning now takes 10 seconds.
Mistakes that undermine the pipeline
- —Editing the loaded Table directly instead of going back into the query to change a step. Direct edits to the loaded Table are silently lost on the next Refresh.
- —Forgetting Close & Load, leaving the query built but not actually in the workbook.
- —Assuming Power Query handles every messy-data scenario. Some problems still need a manual fix or a custom column formula within the query.
Practice this with a real dataset, not a toy example
This exact pipeline-building exercise is Lesson 25 of Advanced Excel for Finance & Business, rebuilding the manual cleaning steps from an earlier lesson so the mapping between what you did by hand and what Power Query automates is direct.
Have a question this guide didn’t answer? See the full FAQ or contact us directly.