Clean in this order
Order matters more than technique. Deduplicating before you normalise keys leaves duplicates behind, because two spellings of the same customer are not yet the same value. Reshaping before you filter means transforming rows you are about to discard. The sequence below exists because each step makes the next one cheaper.
- Take a row count and keep it.
- Remove structural noise — subtotal rows, blank separators, repeated headers.
- Normalise text: trim, clean, case.
- Coerce types: dates, numbers, booleans.
- Deduplicate, on the normalised key.
- Reshape — unpivot wide data into long.
- Reconcile the row count against step one.
1. Structural noise, before anything else
Exports frequently contain rows that look like data and are not: quarterly subtotals styled identically to detail rows, blank separators, and headers repeated every page. Aggregate over them and you double-count, usually by a few percent — small enough that nobody notices for months.
=FILTER(Raw, (Raw[Type]<>"Subtotal") * (Raw[Region]<>""))The reliable tell is a total that never quite ties back to the source system. If your report is consistently out by a similar proportion each period, look for subtotal rows before you look at the formulas.
2. Whitespace and case, the invisible failures
These are the errors that cost the most time because nothing on screen indicates them. A key with a trailing space renders identically to one without.
=TRIM(CLEAN([@Key]))TRIM removes leading, trailing and repeated internal spaces; CLEAN strips non-printing characters, which arrive with almost every export from a legacy system. Neither is optional on a column you intend to join on.
Case is a judgement call: VLOOKUP and XLOOKUP ignore case, EXACT and most database joins do not. If the data is going anywhere else, normalise it with UPPER or PROPER and be consistent.
3. Types, especially dates
Dates are where most of the pain is, because a date stored as text looks like a date and sorts alphabetically. The diagnostic takes a second: dates align right by default, text aligns left.
=DATEVALUE([@RawDate]) <- text to a real date
=VALUE(SUBSTITUTE([@Amount],",","")) <- text to a real numberWatch for the ambiguity that no formula can resolve: 03/04/2026 is March 4th or April 3rd depending on the locale of whoever produced the file. If a source can emit both, insist on ISO format at the export step rather than guessing at the import step.
4. Duplicates, after normalising
Deduplicate on the cleaned key, never on the raw one, and decide explicitly which record wins — usually the most recent.
=COUNTIFS(Data[CleanKey], [@CleanKey]) > 1Flagging duplicates in a column beats deleting them outright. Deletion is irreversible and, more importantly, hides the fact that your source is producing them — which is usually the more valuable finding.
5. Reshape wide data into long
Data with months across the top cannot be grouped by date, which is why so many pivot tables cannot answer the obvious question. Unpivoting turns twelve month columns into two: attribute and value.
In Power Query this is one action — select the month columns, Unpivot. It is the single most useful transformation in reporting, and worth learning even if you use nothing else in Power Query.
6. Reconcile, or you have not finished
Compare the row count and the control total to the numbers you took at step one, and account for every difference. Removed 41 subtotal rows and 12 duplicates? Then the count should be down by exactly 53.
A cleaning process that cannot explain its own row-count change has probably dropped real data. This step takes a minute and is the one people skip.
Do it once, not every month
Everything above is worth doing by hand exactly once. If the same file arrives every week, the steps belong in Power Query, where they are recorded and replayed on refresh — and where a change in the source format fails loudly instead of silently producing wrong numbers.