Power Query is a tool built into Excel (Data tab, Get and Transform Data) that cleans data with clicks and remembers every step. Next month, you press Refresh and the same cleaning happens again.
What Power Query is good for
- Cleaning messy exports from other systems
- Combining many files from one folder
- Reshaping data (unpivot columns into rows)
- Merging two tables, like a lookup without VLOOKUP
- Reports that must be rebuilt every month
First steps
- Select your data and go to Data → From Table/Range. The Power Query editor opens.
- Use the Transform tab: change data types, trim spaces, split a column, remove duplicates.
- Each action is saved as a step in the Applied Steps list on the right. You can click any step to see the data at that point.
- Click Close and Load to send the result to a worksheet.
- Next time, right-click the table and choose Refresh.
Merge and append
Append stacks tables on top of each other, for example January and February sales. Merge joins tables by a common column, like adding the product name from a product list.
Common mistakes
- Leaving the wrong data type, so dates become text
- Hard-coding a file path that later changes
- Forgetting to refresh before sending the report
Go deeper
Power Query is covered in chapter 4 of our Advanced Excel course, from first steps to a refreshable monthly report.