Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.18 End-to-End: Cleaning a Messy Blinkit Export

By Last updated:

Rani receives this raw export from the Blinkit store system (fictional). Let's clean it completely.

Before (Raw sheet)

Order ID Date City Store Customer Phone Amount Status
blk-4001 14.03.2026 pune Kothrud sHRADDHA bAGALE +91 90000 00021 ₹1,299 Delivered
BLK-4002 03/14/2026 Aurangabad CIDCO ZOYA 090000-00022 Rs. 450 delivered
BLK-4001 14.03.2026 pune Kothrud sHRADDHA bAGALE +91 90000 00021 ₹1,299 Delivered
BLK-4003 15-03-2026 Nasik College Road Amir 9000000023 90 Cancelled
BLK-4004 15-03-2026 NAGPUR Sitabuldi raja 91 9000000024 12,990 Delivered
BLK-4005 Kolhapur Tarabai Park Rani 9000000025 240 Returned

After (Clean sheet)

Order ID Date City Store Customer Phone Amount Status Note
BLK-4001 14-03-2026 Pune Kothrud Shraddha Bagale 9000000021 1299 Delivered
BLK-4002 14-03-2026 Sambhaji Nagar CIDCO Zoya 9000000022 450 Delivered
BLK-4003 15-03-2026 Nashik College Road Amir 9000000023 90 Cancelled
BLK-4004 15-03-2026 Nagpur Sitabuldi Raja 9000000024 12990 Delivered Outlier – verify
BLK-4005 Kolhapur Tarabai Park Rani 9000000025 240 Returned Date missing

Steps in Excel – in this order

  1. Protect the raw data: copy the Raw sheet to a new sheet Clean. Note the row count (6) and raw total (not calculable yet – amounts are text).
  2. Order ID: =UPPER(TRIM(A2)).
  3. Duplicates: after step 2, Data › Remove Duplicates on Order ID → 1 removed (BLK-4001), 5 rows remain.
  4. Dates: replace . with -; convert US-style 03/14/2026 with =DATE(RIGHT(B3,4),LEFT(B3,2),MID(B3,4,2)); convert dd-mm-yyyy text with Text to Columns › DMY; leave the blank and add Note "Date missing".
  5. City: mapping table formula from 5.5 → Pune, Sambhaji Nagar, Nashik, Nagpur, Kolhapur.
  6. Customer: =PROPER(TRIM(E2)).
  7. Phone: the 10-digit formula from 5.10.
  8. Amount: the VALUE + SUBSTITUTE formula from 5.6; check =COUNT() = 5.
  9. Status: =PROPER(TRIM(H2)) – "delivered" becomes "Delivered".
  10. Outliers: IQR flag from 5.15 → BLK-4004 (₹12,990) marked "Outlier – verify" (do not delete).
  11. Paste values over the helper formulas, delete helper columns, convert to a Table (Ctrl + T, name tblOrdersClean).
  12. Reconcile: rows = 5 (6 raw − 1 duplicate); total amount = ₹15,069; distinct cities = 5; all phones LEN = 10.

Check totals: 1,299 + 450 + 90 + 12,990 + 240 = ₹15,069.

Ravindra Bagale's Tip

In end-to-end cleaning, many students do the steps in the wrong order – for example, Remove Duplicates before making Order ID UPPER, so "blk-4001" and "BLK-4001" stay separate. Standardise first (trim, case), then duplicates, then types (dates, numbers), and finally reconcile. Remember this order – and if this export comes every day, record all of it in Power Query.

Practice task

Create your own 15-row messy export with at least one example of every problem in this module, clean it following the 12 steps, and write the reconciliation (rows before/after, duplicates removed, total amount, issues flagged).

Thodkyaat sangaycha tar (quick recap)

Raw data kadhich badlu naka; aadhi standardise (TRIM, CLEAN, CHAR(160), case, city mapping), mag duplicates aani blanks, mag types (VALUE, Text to Columns, DATE), mag codes/phones/e-mails, shevti outliers aani errors flag kara – aani rows va totals reconcile kara. Roj yenarya data sathi Power Query. Aata pudhe jaauya – Tables, sorting aani filtering.