Concept
1. What the interviewer is really checking
Not "can you click Remove Duplicates" — but: Do you protect the raw data? Do you ask what the output is for? Do you find problems before calculating? Do you check totals? Can you explain assumptions?
2. The 7-step framework
- Protect — keep the original untouched; work on a copy or in Power Query.
- Clarify — what question must the output answer, for whom, by when?
- Understand — rows, columns, grain ("one row = one invoice line?"), date range, units.
- Profile — list every problem before fixing anything.
- Clean — preferably in Power Query so it's repeatable; document each step.
- Validate — row counts and totals before vs after; cross-check with a known number.
- Answer and communicate — the result, the assumptions, and what you'd fix at the source.
3. The first two minutes — what to say
"Before touching anything I'll keep a copy of the raw file. Can I confirm what this should produce — say, monthly sales by region? I'll first scan the structure: header rows, data types, dates, totals, duplicates, and list issues. Then I'll clean it in Power Query so next month's file can be refreshed, and I'll reconcile the final total."
4. Walk-through: messy_sales_export.csv
First lines:
ABC Traders Pvt Ltd - Sales Register
Period: 01/01/2026 to 31/01/2026
Inv No,Inv Date,Customer,Region,Item,Qty,Amount (Rs)
INV-1001,2026-01-20,Sharma Traders,West,Chair,6,27000
INV-1002,25/01/2026,Sharma Traders,South,Laptop,6,330000
INV-1003,16/01/2026,Sharma Traders,north ,Laptop,4,220000
INV-1004,2026-01-14,Sharma Traders,north ,Desk,2,"24,000"
Problems found (profile step):
| # | Issue | Fix |
|---|---|---|
| 1 | 3 title/blank lines above the header | Remove Top Rows (3), Use First Row as Headers |
| 2 | Dates in 3 formats: 2026-01-20 (11 rows), 25/01/2026 (8), 20-Jan-26 (13) |
Parse each format explicitly (dd/mm/yyyy with locale en-IN); never let ISO dates be read day-first — 2026-01-10 must stay 10-Jan, not 1-Oct |
| 3 | Amount as text with commas ("24,000") |
Remove "," then change type to number |
| 4 | Region case/spaces: north , WEST |
Trim + Proper/Capitalize Each Word |
| 5 | Customer variants: Gupta & Sons, sharma traders |
Trim + Proper; later a customer master |
| 6 | Exact duplicate row (INV-1010 twice) | Remove duplicates on Inv No (after confirming they're true duplicates) |
| 7 | A blank row and a "Sub Total" row with 9,99,999 inside the data | Filter out rows where Inv No doesn't start with "INV" |
| 8 | "Grand Total" row with "x" as amount | Same filter |
Validate: 32 clean invoices · total ₹32,90,500 · North 12,31,000 · South 11,56,500 · West 9,03,000 · all dates within January 2026 (2–30 Jan). Summing the raw column would have added the duplicate (36,000) and the fake subtotal (9,99,999) — or failed on the "x".
Communicate: "Clean total ₹32.9 lakh from 32 invoices. I removed one duplicate and two total rows, standardised regions and customers, and parsed three date formats. Recommendation: ask the ERP team for a single date format and an export without subtotals; meanwhile this Power Query refreshes next month's file."
5. Questions you can ask (they score points)
- "Is Amount before or after GST?"
- "Should cancelled/credit-note invoices be excluded?"
- "Is this the only source, or should it match the accounts total?"
- "Who uses the output, and how often?"
6. If you only have 15 minutes
Do steps 1–4 aloud, fix the issues that affect the answer (headers, amounts, totals rows, duplicates, region names), give the total with a clear "assumptions" line, and list the remaining fixes you'd do with more time.
Common mistakes
Starting with a pivot on raw data. Fixing issues one by one by hand without listing them first. Not reconciling totals. Hiding assumptions.