Cleaning a Messy Import Under Deadline
A hospital finance team must submit a cost report in two days. The source is an export of 48,000 supplier invoice rows with these symptoms: dates appear in at least three formats (some as text 'DD/MM/YYYY', some as Excel serial numbers, some as 'YYYY-MM-DD'); supplier names have trailing spaces and inconsistent casing ('ACME Ltd', 'acme ltd ', 'Acme Limited'); amounts use a mix of '.' and ',' as decimal separators; and 1,100 rows have blank cost-centre codes. The team lead wants to delete the blank-cost-centre rows to 'clean it up fast'.
1. Advise on the blank cost-centre rows: is deletion acceptable, and what should happen instead?
Read the worked answer
Deleting 1,100 rows (2.3% of the file) silently removes real spend from the report and could materially understate totals - and once deleted, the omission is invisible. Instead, the analyst should preserve the rows, flag them as 'cost centre unallocated', and report the unallocated total separately so reviewers see the gap. Deletion is only defensible if the rows are confirmed duplicates or out-of-scope, and that decision should be documented and approved, not made to save time. The report must reconcile: sum of allocated + unallocated = source total.
2. Explain the risk of the mixed date and amount formats and how to clean them safely.
Read the worked answer
Mixed date formats are ambiguous: '03/04/2024' is 3 April or 4 March depending on convention, and Excel serial numbers convert silently but can shift by a day with locale settings. Guessing wrong corrupts period totals. The safe approach is to parse each format explicitly by pattern, convert serials with a known epoch, and spot-check a sample against source documents. For amounts, a value like '1.234,56' vs '1,234.56' means thousands vs. decimal - misparsing changes magnitude by 1000x. Detect the separator convention per row (position of last separator) rather than globally, then validate totals against a control figure.
3. Supplier name inconsistencies will fragment spend. What is the correct remedy and its limit?
Read the worked answer
Normalise (trim whitespace, standardise case) and then map variants to a canonical supplier ID via a lookup table, reviewed by a human for ambiguous cases ('Acme Ltd' vs 'Acme Limited' may or may not be the same entity). The limit: fuzzy matching can wrongly merge distinct suppliers (e.g., two unrelated firms sharing a name stem), so high-confidence auto-matches should be separated from ones needing review. The output should retain the original string for audit and add a canonical ID column.