Smarter Week

How to automate it

How to automate “clean up messy data exports before I can use them”

Here are 2 ways to spend less time on this, best first. Each comes with steps you can follow today and, for AI fixes, a prompt to copy.

60 min
typically, a few times a week
65%
of the time can be automated
Some setup
to set up

Fix 1 of 2

AutomationBest fix

Record your clean-up steps once in Power Query so they replay

Power Query (Excel and Power BI) records each cleaning step; next time you drop in the new export and click Refresh.

Typically saves about 70% of the time2 h to set up
  1. 1Open the export in Excel: Data > Get Data > From File.
  2. 2Do your cleaning steps in the Power Query editor (remove columns, fix types, merge).
  3. 3Save and load; next time replace the source file and click Refresh.
  4. 4Ask AI for help with any step you cannot find ("how do I unpivot in Power Query?").

Tools: Excel Power Query · Power BI · Google Sheets with Apps Script or connected sheets

Fix 2 of 2

AI

Have AI clean and standardize the export

AI can fix inconsistent names, dates, duplicates and formats in an export and tell you exactly what it changed.

Typically saves about 50% of the time5 min to set up
  1. 1Remove anything confidential your AI policy does not allow.
  2. 2Upload the file to Copilot in Excel, Gemini in Sheets, or an approved ChatGPT or Claude account.
  3. 3Use the prompt below.
  4. 4Check the change log and spot-check rows before using the data.
Prompt to copy
Clean this dataset: standardize [COLUMN] values (e.g. company names, states), convert all dates to YYYY-MM-DD, trim spaces, flag likely duplicates (do not delete them), and split [COLUMN] into [PARTS]. Return the cleaned file and a change log listing every type of change and how many rows it affected.

Tools: Copilot in Excel · Gemini in Google Sheets · ChatGPT or Claude with file upload

Quick wins

Have you tried…

Have you recorded your data clean-up steps in Power Query so they replay with one click?
Power Query in Excel remembers each cleaning step. Next time you drop in the new export and click Refresh.

Who does this task

Roles in our library that list this as one of their common tasks. Each guide covers the rest of that role’s week.

Business analystOperations coordinator

HourLeak · the 8-minute work audit

How many hours does this cost you?

The free 8-minute check works out where your week goes and gives you your top fixes. The team scan does the same for everyone and adds it up, so you know which leaks to fix first.

Take the free check Scan my team, $49

Answers are anonymous. Leaders only see team totals.

Other common tasks for Business analysts