15. Final Project: Blinkit Maharashtra Monthly Report
15.2 Stage 1 – Clean the Raw Export
Steps in Excel (choose formulas or Power Query)
- Formula route (Module 5.18): copy
Raw_OrderstoClean› helper columns:=UPPER(TRIM(A2))for Order ID,=PROPER(TRIM(C2))for City, a mapping-table lookup to change Aurangabad → Sambhaji Nagar,=VALUE(SUBSTITUTE(SUBSTITUTE(H2,"Rs.",""),",",""))for Amount, date fix withDATEor Text to Columns (DMY). - Remove duplicates: Data › Data Tools › Remove Duplicates on Order ID after the IDs are standardised.
- Missing values: leave blank Delivery Mins blank and flag them (
=IF(I2="","Missing","")) – don't type 0. - Power Query route (Module 10): Data › Get & Transform Data › From Table/Range › Trim, Clean, Capitalize Each Word, Replace Values, Change Type with Locale (English (India)), Remove Duplicates › Close & Load To… a Table.
- Reconcile (ताळमेळ): row count before/after, duplicates removed, total Amount before/after (Module 5, golden rules).
Worked example – reconciliation box.
| Check | Raw | Clean | Comment |
|---|---|---|---|
| Rows | 412 | 405 | 7 duplicates removed |
| Distinct cities | 11 spellings | 6 | Mapping applied |
| Amount stored as text | 38 | 0 | Converted |
| Missing Delivery Mins | 9 | 9 (flagged) | Not filled with 0 |
Ravindra Bagale's Tip
Khup students Remove Duplicates aadhi chalvtat aani mag Order ID clean kartat – "blk-9001" aani "BLK-9001" vegle samjale jatat aani duplicate rahto. Kram lakshat theva: aadhi standardise (TRIM, UPPER, PROPER), mag duplicates. Aani reconciliation table nakki banva – interviewer la hech aavadta.
Practice task
Clean Raw_Orders by either route, fill in the reconciliation table with your real counts, and save the clean data as a Table named tblOrders.