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
Many students run Remove Duplicates first and clean the Order ID afterwards – "blk-9001" and "BLK-9001" are treated as different and the duplicate stays. Remember the order: standardise first (TRIM, UPPER, PROPER), then duplicates. And always build a reconciliation table – interviewers love it.
Ravindra Bagale's Tip – मराठी
बरेच students Remove Duplicates आधी चालवतात आणि मग Order ID clean करतात – "blk-9001" आणि "BLK-9001" वेगळे समजले जातात आणि duplicate राहतो. क्रम लक्षात ठेवा: आधी standardise (TRIM, UPPER, PROPER), मग duplicates. आणि reconciliation table नक्की बनवा – interviewer ला हेच आवडतं.
Ravindra Bagale's Tip – हिंदी
बहुत से students पहले Remove Duplicates चलाते हैं और फिर Order ID clean करते हैं – "blk-9001" और "BLK-9001" अलग माने जाते हैं और duplicate रह जाता है. क्रम याद रखो: पहले standardise (TRIM, UPPER, PROPER), फिर duplicates. और reconciliation table ज़रूर बनाओ – interviewer को यही पसंद आता है.
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.