15. Final Project: FreshGo 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 |
About this data: Teaching sample for a fictional shop “CityBasket Retail”. Figures and names (e.g. Meera Joshi) are made up for this lesson on stage 1 clean the raw export.
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.
Loading practice tasks...
- Total quantity (Easy): Add up the "Quantity" column. (sheet "Data")
- Average unit price (Easy): Average of the Unit price ($) column, rounded to 2 decimals. (sheet "Data")
- Rows in Website (Easy): How many rows have Channel "Website"? (sheet "Data")
- Largest quantity (Easy): The largest value in the "Quantity" column. (sheet "Data")
- Clean name (Easy): Trim the "Name" value in the first row. (sheet "Text")
- Proper case (Easy): the "Name" value in the first row trimmed and in proper case. (sheet "Text")
- Code prefix (Easy): First 3 characters of the "Channel" value in the first row. (sheet "Text")
- Code number (Easy): Last 4 characters of the "Channel" value in row 3. (sheet "Text")
- Quantity for Website (Medium): Total of the Quantity column where Channel is "Website". (sheet "Data")
- Website in Austin (Medium): Total of the Quantity column where Channel is "Website" and City is "Austin". (sheet "Data")
- City part (Medium): Characters 5 to 7 of the "Channel" value in row 4. (sheet "Text")
- Name length (Medium): Length of the "Name" value in row 3. (sheet "Text")
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
