Do you download the same report every week, then spend half an hour deleting blank rows, fixing dates and renaming columns before you can use it? Power Query can do that tidy-up for you, every time, with a single click.
What is Power Query?
Power Query is a tool built into Excel (and Power BI) for getting data and cleaning it up. Microsoft’s Power Query documentation covers every feature in detail. You’ll find it on the Data tab, in the Get & Transform Data group. The key idea is simple: every change you make is recorded as a step. Next time, Power Query replays those steps on the new data automatically.
A typical example
Imagine a weekly sales export from your finance system. It has two lines of junk at the top, names with stray spaces, dates stored as text, and the occasional duplicate row. In Power Query you would:
- Load the data using From Table/Range or Get Data, From File.
- Remove the top rows and use the first remaining row as headers.
- Trim and clean the text columns to get rid of stray spaces.
- Change the date and amount columns to the correct data types.
- Remove duplicate rows.
- Click Close & Load to send the clean table back to Excel.
Each of those actions appears in the Applied Steps list on the right. You can rename them, reorder them or delete one if you change your mind.
Next week: just press Refresh
When next week’s file arrives, save it over the old one (or in the same place with the same name) and go to Data, Refresh All. Power Query runs every step again. Half an hour becomes a few seconds, and you get the same result every time, with no missed steps.
Combining lots of files at once
If you receive a separate file each month, use Get Data, From File, From Folder. Power Query will stack all the files in that folder into one table. Drop a new file in the folder, refresh, and it’s included.
Why it’s worth learning
- It saves time on repetitive cleaning tasks.
- It reduces errors, because the same steps run the same way every time.
- It leaves your original data untouched, so you can always start again.
- It works in Power BI too, so the skills carry straight over.
Power Query is a big part of my Excel Advanced and Power BI courses. If you’d like to stop doing the same tidy-up every week, take a look.