SMEMindsData · Beginner · about 18 minutes
Cleaning a messy sheet — the two hours before analysis can even start
You export sales data. 'Sharma Traders', 'Sharma traders', 'SHARMA TRADERS' and 'Sharma Traders ' are four customers as far as your pivot table is concerned. Half your dates are text.
- You need
- Data: monthly sales export from Tally, 2,400 rows. Want to do: sales by customer by month, and top 20 customers.
- Tool
- Any assistant, with 30–50 sample rows
- You get
- 1. COLUMN AUDIT Date — should be date; is mixed text/date in three formats (dd/mm/yyyy, d/m/yyyy, dd-mm-yyyy).
- Time saved
- First clean: 2 hours to about 40 minutes. Every subsequent month: 2 minutes with Power Query.
- 1Copy the file
- 2Sample 40 rows
- 3Diagnose
- 4Get the recipe
- 5Apply to the copy
- 6Check counts
- 7Approve merges
- 8Save the recipe
Check the output
- Answer the Sharma Traders, Nashik question yourself before any merge. This is a business fact the model cannot know, and getting it wrong corrupts the top-20 list either way.
- Check the row count before and after every fix. A cleaning step that drops rows is the most common silent failure.
- After the customer-name fix, count distinct customers before and after. If it fell from 340 to 180, something over-merged.
- Ask specifically for the list of ambiguous dates in the full file, and resolve them against the source system rather than assuming.