Getting Started with Power Query in Excel
If you clean the same messy CSV export every week — deleting header junk, splitting columns, fixing dates, removing blanks — Power Query will give you those hours back. You perform the cleanup once, Excel records every step, and from then on it is a one-click refresh. It is built into Excel 2016 and later under Data > Get & Transform.
The mental model
Power Query is a recorded recipe. You connect to a source (file, folder, database, web page), apply transformation steps in an editor, and load the result to a worksheet or the data model. The original source is never modified. When the source updates, Data > Refresh All replays the recipe.
Your first query in five minutes
- Data > Get Data > From Text/CSV and pick your export file.
- Click Transform Data (not Load) to open the Power Query Editor.
- Do your usual cleanup with the Ribbon: remove top rows, promote headers, change column types, filter out blanks, split a column by delimiter.
- Watch the Applied Steps list on the right grow — that is your recipe, and every step is editable or deletable later.
- Close & Load. Your clean data lands in a new sheet as a Table.
Next week, when the new export arrives: overwrite the old file with the new one (same name, same folder), open your workbook, and hit Refresh. Done.
The transformations that matter most
- Use First Row as Headers — because exports never come with proper headers.
- Change Type — make dates be dates and numbers be numbers, permanently.
- Filter rows — drop subtotal lines, blanks, and junk once, forever.
- Split / Merge Columns — take "Last, First" apart or put it back together.
- Unpivot Columns — the star of the show. Turns a wide month-per-column report back into tidy rows that pivot tables can actually use. Select the month columns, right-click, Unpivot. Life-changing.
- Append / Merge Queries — stack twelve monthly files into one table, or join two tables like a database.
From folder: the weekly-report killer
Point Get Data > From File > From Folder at a folder of identically-shaped files and Power Query will combine every file into one table — and automatically include new files dropped into the folder on the next refresh. This single feature replaces entire copy-paste rituals.
When to use Power Query vs formulas
Formulas are for calculations that live with the data. Power Query is for getting the data into shape before any calculation happens. The rule of thumb: if you would have to repeat the manual cleanup when new data arrives, it belongs in Power Query.