Direct answer: Power Query records each cleaning step you perform on data, so the same steps rerun automatically when you click Refresh. Find it on Data > Get Data.
A simple example
- Data > Get Data > From Workbook (or CSV).
- In the editor, remove unused columns and set data types.
- Trim text, replace errors, split a “City, State” column.
- Close & Load to a table.
Next month, replace the file and click Refresh; every step runs again.
Merge vs append
- Append stacks tables with the same columns (twelve monthly files into one).
- Merge joins tables on a key, like a lookup that returns many columns.
Unpivot
A report with months across columns can be turned into one row per month with Unpivot Columns, which is the shape pivots and Power BI prefer.
Common mistakes
- Wrong data types, which makes joins fail silently
- Merging on a key with duplicates, multiplying rows
- Hard-coding file paths instead of using a folder query
Best practices
Keep raw data untouched, name steps, and load to the Data Model for large sets. The same editor is in Power BI.
Conclusion
If you repeat any cleaning task monthly, automate it. Learn it properly in the Power Query course.
Frequently asked questions
Where is Power Query in Excel?
On the Data tab: Get Data and Transform Data (Excel 2016 and later, Microsoft 365).