Every month the same export lands in your inbox, and every month you clean it the same way: delete three columns, split the name field, filter out the blanks, fix the date format. Power Query records that cleanup once and replays it on next month’s file with a single click. It’s Excel’s built-in tool for reshaping messy data into something usable, and the whole point is that you set up the steps one time and never repeat the manual work again. This walks through loading data, the transforms you’ll use most, and the refresh that makes it all pay off.
Load your data into the editor
Power Query lives on the Data tab — no add-in needed in Excel 2016 or later. You point it at a source, and it opens a separate editor where the cleaning happens without touching your original data.
- Click anywhere in your table, then go to Data → Get Data → From Table/Range (or From File for a CSV).
- The Power Query Editor opens in its own window, showing a preview of your data.
- Every change you make here is recorded as a step — your source stays untouched until you load the result back.
That separation is the first thing to understand: you’re not editing the spreadsheet, you’re building a recipe that produces a clean spreadsheet. The original messy export sits exactly where it was, and Power Query reads from it. This is why the tool is safe to experiment in — a transform that goes wrong changes nothing permanent, and you can delete the step and try again. Microsoft’s guide to transforming data with Power Query covers the full editor when you want depth.
The transforms you reach for first
Most cleanup is a handful of operations applied in sequence, and each is a button on the Home or Transform tab — no formula written.
| Transform | What it does |
|---|---|
| Remove Columns | Drops fields you don’t need from the output |
| Filter Rows | Keeps only rows matching a condition (like AutoFilter, but recorded) |
| Change Type | Sets each column to text, number, or date so the data behaves |
| Split Column | Breaks “Smith, John” into two fields by a delimiter |
| Remove Duplicates | Strips repeated rows in one click |
You apply these by selecting a column and clicking the operation — Power Query shows the result in the preview immediately. Change Type is the one beginners skip and regret: if a date column comes in as text, every later step that treats it as a date fails, so set types early. String four or five of these together and a genuinely messy export becomes clean in a minute, with each move captured for reuse.
Applied Steps: your cleanup, recorded
Down the right side of the editor is the Applied Steps pane — a numbered list of every transform you’ve made, top to bottom. This is the heart of Power Query, and it’s what separates it from cleaning data by hand.
Because the steps are saved with the query, they become a repeatable recipe rather than a one-off effort. That’s the mental shift: you’re not cleaning this file, you’re defining how files of this shape get cleaned. Next month’s export — same columns, new rows — runs through the identical steps untouched. It’s the same logic that makes a recorded macro powerful, but with a visible, editable history instead of hidden code.
Unpivot: the transform that earns its keep
The single most valuable Power Query trick turns a human-friendly cross-tab into the tidy, one-record-per-row layout that pivot tables and charts need. Select the columns to collapse, then Transform → Unpivot Columns.
Region | Jan | Feb | Mar
North | 120 | 90 | 110
South | 80 | 95 | 100
Region | Month | Sales
North | Jan | 120
North | Feb | 90
South | Jan | 80 …
The wide three-month grid becomes a tall list with one row per region-month, which is exactly what a pivot table wants as its source. Reports built for people to read are almost always the wrong shape for software to analyze, and unpivot bridges the two without a single formula. Once you’ve hit a dataset that needed it, you’ll spot the pattern everywhere — and reach for Unpivot Other Columns to collapse everything except your key fields.
Merge and append: combine your sources
Real analysis often spans more than one table, and Power Query joins them two ways. The distinction is worth getting straight before you start.
| Operation | Use it to… |
|---|---|
| Append | Stack tables with the same columns — January + February + March into one |
| Merge | Join tables side by side on a shared key — add customer names from a lookup table |
Append is the one that quietly saves hours: point it at a folder of monthly files and Power Query stacks them all into one query, then re-stacks automatically when you drop a new file in. Merge is Excel’s version of a database join — it’s what you’d otherwise build with a stack of lookup formulas, done once and refreshed on demand. Between them, “I have data in five places” stops being a manual copy-paste job.
Add a column without wrestling a formula
When the clean data needs a value the source doesn’t have — a total, a category, a flag — Add Column → Custom Column builds it with a short expression that reads more plainly than a worksheet formula:
= [Quantity] * [UnitPrice]Reference fields by name in square brackets and Power Query computes the new column for every row, recording it as one more applied step. There’s no fill-down, no $ anchoring, no range to extend as data grows — the step simply applies to whatever rows arrive on refresh. For conditional values, the Add Column → Conditional Column dialog builds if-then logic from dropdowns without any code at all, which covers most “flag the rows over budget” needs before you ever touch the expression bar.
Close, load, and refresh forever
When the steps are right, click Close & Load on the Home tab to send the result back to Excel — as a table on a new sheet, or as a connection that feeds a pivot or the data model.
That refresh is the whole return on the setup. The first run takes ten minutes of clicking through transforms; every run after that takes one click, forever. A clean Power Query output makes an ideal feed for a live dashboard or a pivot report, because the data arrives already shaped and the whole chain updates together. Build the query once, and the monthly data chore you used to dread becomes something you barely think about — point it at the new file, refresh, done.