How to automate repetitive cleaning tasks for recurring clients

Every month, the same client sends the same export — sales_2026_06.csv, then sales_2026_07.csv — and every month you do the same eight things to it by hand: promote the header, delete the blank spacer rows, fix the text dates, dedupe on Order ID, map the region names, drop the test orders, and reconcile the total. Ninety minutes, click for click, thirty-one times identical. Across a year that's roughly eighteen hours spent re-deciding things you already decided last January. The cleaning isn't the problem. Doing it manually on a file that never structurally changes is the problem, and it's the most automatable work you own.

Manual is the right tool once, and the wrong tool twice

Cleaning a one-off file by hand is fine — you'll never see it again. But a recurring client's file has a fixed shape: the same columns, the same quirks, the same fixes, month after month. Doing that by hand isn't diligence, it's a subscription you pay in your own time, and it scales the wrong way — the more recurring clients you win, the more of your month vanishes into repeating yourself. The goal is to build the cleaning once, as steps a machine can replay, and then spend your recurring hours on the analysis the client actually values.

The example: eight manual steps become one refresh

Power Query is built exactly for this, and it records every click as a reusable step. Point it at the file — Data → From Text/CSV — and each fix you make is saved in order:

Query: clean_sales
  1. Promoted headers
  2. Removed blank rows
  3. Changed type: Order Date -> Date
  4. Removed duplicates on Order ID
  5. Merged Region with region_map -> clean region
  6. Filtered out rows where Customer = "TEST"
  7. Reordered / renamed columns
  (source = sales_2026_06.csv)

Next month, you don't repeat any of it. Replace the source path — or better, point the query at a parameter so you change it in one box — and hit Refresh. All seven steps re-run against sales_2026_07.csv in a couple of seconds, in the same order, with the same rules. The ninety-minute ritual becomes: swap the file, click Refresh, glance at the reconciliation. What used to be an afternoon is now a coffee.

How to automate a recurring client's cleaning

1. Record the steps once the next time you clean the file manually — in Power Query, a macro, or a saved recipe — instead of just doing them and closing the file.
2. Parameterize the input. Put the file path in one parameter so next month is a single change, not a hunt-and-replace through the steps.
3. Move fixed decisions into lookup tables — region maps, product codes — so a new value is a table edit, not a rewrite of the logic.
4. Build in the checks. Add a step that flags unmapped values or a row count outside the usual range, so the automation tells you when this month's file breaks the pattern.
5. Document what it does in one line at the top, so future-you (or a collaborator) can trust the pipeline without reverse-engineering it.

The one rule that makes automation safe: never let it run silently on a file it can't handle. A pipeline that refreshes without checking is how last month's typo becomes this month's wrong dashboard — the flags in step four are what keep the speed honest.

Automation is a raise you give yourself

Here's the quiet economics of it. If a recurring client pays a flat monthly fee and you cut the cleaning from ninety minutes to ten, you didn't lower the price — you raised your effective rate on that account by a factor of five, every month, forever. The client gets the same clean data on the same day; you get eighty minutes of your life back and the capacity to take another client without adding another afternoon. Repetitive cleaning is the clearest signal in independent analytics that a task is ready to be built once and replayed, and the analysts who scale are simply the ones who stopped retyping the same fixes into the same file.