Getting Started With Power Query in Excel
The Excel tool everyone means to learn and never quite gets to
By Shehryar Hassan, Microsoft 365 & Azure Consultant
If you've ever pasted a report into Excel and spent twenty minutes cleaning it up before you could even look at it, Power Query is the thing that should have saved you that time. It's been built into Excel for years, sitting under Data, Get and Transform Data, and most people never open it.
Power Query isn't a formula and it isn't a macro. It's a set of repeatable steps you record once, against a specific data source, that you can then re-run whenever the data changes. Pull in a CSV export from your payroll system, split a column, remove some blank rows, rename a few headers, and Power Query remembers every one of those steps. Next month, when you get a new export, you click Refresh and it does the whole thing again in seconds.
Where to start
The easiest way in is Data, Get Data, From File, From Text/CSV. Pick a file you already clean up by hand regularly, something with a predictable structure. Power Query opens a preview window and an Applied Steps panel on the right. Every click you make, removing a column, filtering rows, splitting text, gets added there as a named step.
Don't worry about getting it perfect the first time. You can click any step in that list to jump back to that point, and you can delete a step without redoing the ones after it. This is the part people don't expect, it's forgiving to experiment in.
Where it actually pays off
The real value shows up the second time you use the same query. Load new data into the same folder or paste it into the same sheet range, click Refresh, and every step replays automatically. If you're rebuilding the same pivot ready table from a fresh export every week, that's the habit worth breaking.
It also handles things formulas struggle with, merging two tables on a shared column, unpivoting wide data into a long format, or pulling data straight from a SharePoint list without exporting anything at all.
If you only try one thing, load a CSV you already reformat by hand, apply the cleanup as query steps instead of manual edits, and save it. Next time that file lands in your inbox, you'll see why it was worth the first fifteen minutes.
Get new posts by email
One note when there is something worth reading. No spam, unsubscribe anytime.
Related articles
What Clipchamp Is and Why It's Suddenly on Your Windows Start Menu
Clipchamp didn't require an install, it just appeared on people's machines. Here's what it actually is, what a Microsoft 365 subscription adds to it, and when it's worth reaching for instead of a real editing tool.
Why Your Excel Conditional Formatting Rules Stop Working
If your conditional formatting rules used to work and now don't, the cause is almost always overlapping rules left behind from copy and paste, not a broken feature.
Why Your Word Document Opens in Protected View (and When It's Safe to Turn Off)
Word's Protected View isn't a glitch, it's a safety check for files that come from outside your trusted circle. Here's why it happens and when it's actually fine to click through it.