Excel Power Query: clean and combine data once, refresh forever
Every month you get the same messy export and spend an hour cleaning it. Power Query records those steps once. Next month: paste in the new file, click Refresh, done.
Your first query
- Click inside your data › Data › From Table/Range (or Data › Get Data › From File for a CSV/Excel file).
- The Power Query Editor opens. Each change you make is listed under Applied Steps on the right.
- Clean the data (below).
- Home › Close & Load. The clean data lands on a new sheet as a table.
The steps you’ll use most
| Job | Where |
|---|---|
| Delete columns | select › right-click › Remove |
| Use first row as headers | Home › Use First Row as Headers |
| Remove blank rows | Home › Remove Rows › Remove Blank Rows |
| Split a column | Home › Split Column › By Delimiter |
| Trim spaces | Transform › Format › Trim |
| Fix data type | click the ABC/123 icon on the header |
| Filter rows | header arrow, like a normal filter |
| Remove duplicates | right-click the column › Remove Duplicates |
Made a mistake? Click the × next to a step in Applied Steps.
Refresh with new data
Replace the source (same file name and columns) › Data › Refresh All (Ctrl+Alt+F5). Every step runs again.
Combine many files
Put all monthly files in one folder › Data › Get Data › From File › From Folder › Combine & Transform. One table from all of them — and new files are picked up on refresh.
Look up from another table
Home › Merge Queries joins two tables on a matching column — like XLOOKUP without formulas.
Related: CSV to Excel · pivot tables (load the result straight into one)
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
