Data cleaning vs data transformation: what the difference costs you

You quoted "clean up the sales data and build a trend chart" as a two-hour job. The cleaning genuinely took forty minutes — a text date column, a few duplicates, done. Then it turned out the client's file had a column for every month across the top, so before you could chart a trend you had to reshape the whole thing from wide to long, then add a three-month moving average, then a year-on-year comparison "while you're at it." Eight hours later you understood the mistake: you priced cleaning and delivered transformation, and the two are not the same job. One is bounded. The other is a door the client can keep opening.

Cleaning has an end; transformation has a next request

Cleaning makes existing data correct and consistent: fixing types, removing duplicates, standardizing categories, handling blanks. It has a natural finish line — the data is either right or it isn't, and once it's right there's nothing left to clean. Transformation reshapes and derives new data to answer a question: pivoting, unpivoting, joining, aggregating, bucketing, building calculated fields. It has no natural end, because every answer suggests another question, and each "can we also see…" is a fresh transformation. That difference — bounded versus open-ended — is the entire reason they cost different amounts and must be quoted apart.

The example: the same file, two very different jobs

Cleaning the date column is bounded — one operation, then finished:

CLEANING:  "03/2026" stored as text  ->  real date, done

Transformation is where the hours hide. The client's sales table is wide, a column per month, which no charting tool will trend:

Region   Jan    Feb    Mar
North    1200   1400   1600
South     900   1100    950

To draw a trend you have to unpivot it into long form — one row per Region-and-month:

Region   Month   Sales
North    Jan     1200
North    Feb     1400
North    Mar     1600
South    Jan      900   ...

In Power Query that's Select the month columns → Unpivot Columns, and now the data can be charted. But notice what happens next: "add a rolling three-month average" is another transformation (=AVERAGE(OFFSET(cell,0,0,-3,1)) per row), "show year-on-year" is another, "bucket regions into tiers" is another. The cleaning ended at the date fix. The transformation requests keep arriving, and each one is real work you never quoted, because it all got filed under the friendly word "clean up."

How to separate them on the quote

1. Split the line in two. "Data cleaning: make the supplied file correct and consistent" and "Data transformation: reshape and derive fields for the requested analysis" are different deliverables with different prices.
2. Fix the scope of cleaning to the file as supplied — it's bounded, so you can quote it flat with confidence.
3. Quote transformation per output. One reshape and two derived metrics is a defined amount of work; "and also…" is a new output at the same rate.
4. Name the reshape explicitly when you spot it. "This file needs pivoting to long form before it can be charted — that's transformation, half a day" turns a silent surprise into a line item.
5. Treat new questions as new work. A moving average the client didn't mention at kickoff is a change request, priced like one, not a favour folded into "cleaning."

The word "clean" is where the money leaks

Clients say "clean it up" to mean everything between the raw file and the finished chart, and if you accept that framing you've agreed to an unbounded job at a bounded price. You don't need to lecture anyone on the taxonomy — you just need to hear "clean" and quietly ask yourself which half they mean, then quote both. Cleaning is the bounded part you can promise flat; transformation is the open-ended part that deserves its own number and its own change-request rate. Confuse the two on the quote and the transformation hours come out of your evening; separate them and each one gets paid for the work it actually is.