5.18 End-to-End: Cleaning a Messy FreshGo Export
Rani receives this raw export from the FreshGo 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 |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Vikram Rao) are made up for this lesson on end to end cleaning a messy FreshGo export.
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 |
About this data: Demo table only — not real customer or company data. Built to practise end to end cleaning a messy FreshGo export.
Steps in Excel – in this order
- Protect the raw data: copy the
Rawsheet to a new sheetClean. Note the row count (6) and raw total (not calculable yet – amounts are text). - Order ID:
=UPPER(TRIM(A2)). - Duplicates: after step 2, Data › Remove Duplicates on Order ID → 1 removed (BLK-4001), 5 rows remain.
- Dates: replace
.with-; convert US-style03/14/2026with=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". - City: mapping table formula from 5.5 → Pune, Sambhaji Nagar, Nashik, Nagpur, Kolhapur.
- Customer:
=PROPER(TRIM(E2)). - Phone: the 10-digit formula from 5.10.
- Amount: the VALUE + SUBSTITUTE formula from 5.6; check
=COUNT()= 5. - Status:
=PROPER(TRIM(H2))– "delivered" becomes "Delivered". - Outliers: IQR flag from 5.15 → BLK-4004 (₹12,990) marked "Outlier – verify" (do not delete).
- Paste values over the helper formulas, delete helper columns, convert to a Table (Ctrl + T, name
tblOrdersClean). - 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.
Ravindra Bagale's Tip – मराठी
End-to-end cleaning मध्ये बरेच students steps चुकीच्या order मध्ये करतात – उदाहरणार्थ Order ID UPPER करण्याआधी Remove Duplicates, मग "blk-4001" आणि "BLK-4001" वेगळे राहतात. आधी standardise (trim, case), मग duplicates, मग types (dates, numbers), आणि शेवटी reconcile. ही order लक्षात ठेवा – आणि हा export रोज येत असेल तर हे सगळं Power Query मध्ये record करा.
Ravindra Bagale's Tip – हिंदी
End-to-end cleaning में बहुत से students steps गलत order में करते हैं – जैसे Order ID को UPPER करने से पहले Remove Duplicates, फिर "blk-4001" और "BLK-4001" अलग रह जाते हैं. पहले standardise (trim, case), फिर duplicates, फिर types (dates, numbers), और आख़िर में reconcile. यह order याद रखो – और अगर यह export रोज़ आता है तो यह सब Power Query में record करो.
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).
Loading practice tasks...
- Clean order ID (Easy): Clean A2.
- Clean customer name (Easy): Clean C2.
- Clean city (Easy): Clean D2.
- Clean another ID (Easy): Clean A4.
- Amount as a number (Medium): Change E4 into a number.
- US date to real date (Medium): Make a real date from B5.
- Name with a double space (Medium): Clean C5.
- Orders from Chicago (Medium): How many cleaned cities in D2:D6 are "Chicago"?
- Total amount (Hard): Total of E2:E6 as numbers.
- New York sales (Hard): Total amount for New York (cleaned D2:D6).
- Clean label (Hard): Make "GX-9002 | Raj Patel | Chicago" from row 3.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
