Power Query: Get & Transform Data
Power Query is Excel’s built-in data-cleaning engine — import messy files, reshape them with recorded steps, and refresh the whole clean-up with one click.
What you will learn
- Explain what Power Query is for
- Import and clean a CSV with recorded steps
- Refresh the clean-up when the source changes
The modern way to clean data
Cleaning data by hand — deleting columns, trimming spaces, splitting names — works once, but you must redo it every time the file changes. Power Query (also labelled Get & Transform on the Data tab) is a tool built into Excel that records every clean-up step you take. Next month, point it at a fresh file and replay all the steps with one Refresh. You set up the clean-up once and reuse it forever.
Think of it as a recipe: each action you do (remove a column, trim text) is written down as a step, and the recipe can be re-run on new ingredients (new data) any time.
Watch out: Power Query lives in Excel for Windows (2016+) and Microsoft 365, and in Excel for Mac (recent versions). It is a desktop-Excel feature; the formulas elsewhere in this course also work in Google Sheets, but Power Query is specific to Excel.
A worked clean-up
Imagine a messy sales.csv exported from another system. It has extra spaces in the Region names, a useless Notes column, and mixed letter-casing. Here is the whole flow from import to clean Table:
- Go to Data > Get Data > From Text/CSV (or From File), pick
sales.csv, and click Transform Data. This opens the Power Query Editor — a separate window showing a preview of your data. - Right-click the useless Notes column heading and choose Remove. A step appears in the Applied Steps list on the right.
- Select the Region column, then Transform > Format > Trim to strip stray spaces. Another step is recorded.
- Still on Region, choose Transform > Format > Capitalize Each Word so casing is consistent. Another step.
- Click Close & Load. The cleaned data lands back in Excel as a proper Table.
Applied Steps:
Source
Removed Columns (Notes)
Trimmed Text (Region)
Capitalized Each Word (Region)Note: Output: A clean table loads into Excel: no Notes column, Region values trimmed and tidily capitalised (North, South, …), ready to pivot. Crucially, those four steps are saved with the workbook.
The payoff: one-click refresh
Next month a new sales.csv arrives with the same messy shape. You do not repeat the clean-up. You replace the file and click Data > Refresh All — Power Query re-runs the recorded steps on the new data automatically.
Note: Output: The table reloads, fully cleaned: the new month’s rows have their Notes column removed, Region trimmed and capitalised, exactly as before. A clean-up that took several clicks the first time now takes one — every month, forever.
What else Power Query does well
| Task | What Power Query does |
|---|---|
| Combine files | Stack many CSVs from a folder into one table |
| Merge tables | Join two tables on a shared column (like a lookup) |
| Unpivot | Turn wide month-columns into tidy rows for analysis |
| Split / extract | Break a column (like “Last, First”) into parts |
| Change types | Make sure dates are dates and numbers are numbers |
Unpivot deserves a special mention: spreadsheets people send often have one column per month (Jan, Feb, Mar…), which is hard to chart or pivot. Power Query’s Unpivot flips that wide layout into tidy (Month, Value) rows in two clicks — a transformation that is painful to do by hand.
Tip: Every step is editable. Click a step in the Applied Steps list to see the data at that point, delete a step you got wrong, or drag to reorder. Because the original file is never changed, you can experiment freely — Power Query only reshapes a copy on its way into Excel.
Q. What is the biggest advantage of cleaning data with Power Query instead of by hand?
✍️ Practice
- Save a small messy CSV (with an extra column and spaced-out region names), import it with Data > Get Data > From Text/CSV, and clean it in the Power Query Editor.
- Edit the CSV to add a few rows, then use Refresh All and confirm the same clean-up applies to the new rows.
🏠 Homework
- Take any CSV with at least three columns. In Power Query, remove one unneeded column, trim a text column, and set a column’s type to a proper number or date. Load it, then describe each step in the Applied Steps list in your own words.