Power Query: clean a report once, refresh it forever
This is the single biggest time-saver for any report you build more than once. Power Query records every cleanup step you take — and next month, you drop in the new file and hit Refresh. The work you did by hand becomes a button.
The idea
Most monthly reports are the same ten cleanup steps on a fresh export: remove junk columns, split a field, trim spaces, filter out blanks, rename headers. Power Query captures those steps as a recipe. Change the source file, refresh, and the whole recipe re-runs in order.
Getting started
- Click inside your data, then Data → Get Data → From Other Sources → From Table/Range (or From File to pull straight from a CSV/Excel export).
- The Power Query Editor opens. Do your cleanup here with the ribbon buttons — remove columns, split columns, replace values, trim, filter rows, change data types.
- Each action appears in Applied Steps on the right. That list is your recipe — you can rename, reorder, or delete any step.
- Close & Load to drop the cleaned result back into a sheet.
Next month
Replace the source file (or paste the new export into the same table) and click Data → Refresh All. Every step replays on the new data. No re-doing the cleanup, no copy-paste errors.
Moves that pay off immediately
- Unpivot a cross-tab (months across the top) into a tidy long table that a PivotTable can actually use.
- Merge two queries to join tables on a key — a lookup that survives new rows.
- Append twelve monthly files into one dataset in a few clicks.
Where it trips up
Power Query is case- and type-sensitive: set your data types early, and watch for a column that arrives as text one month and number the next. And keep step names meaningful — "Removed Columns" is fine, but future-you will thank present-you for a quick rename when a step does something non-obvious.
Doing this every month across several entities is exactly the grind IMS Made Simple and TrueBalance were built to remove. Once your data's clean, summarize it with a PivotTable in four clicks.