7. Data Cleaning A–Z in Power Query
7.25 Case Study: Cleaning a Messy Blinkit Export End to End
Scenario. Rani (state operations head) sends Blinkit_Maharashtra_Export.xlsx. It has 2 title rows, a total row, mixed city spellings, ₹ amounts as text, Indian dates, UTC times for some rows, duplicate lines and bad values. (Fictional practice file.)
Before (sample)
| Order No | Order Date | City | Amount | Mins | Phone |
|---|---|---|---|---|---|
| blk-81001 | 20-10-2025 | ·pune | ₹1,250 | 9 | +91 98220 12345 |
| BLK-81001 | 20-10-2025 | Pune | ₹1,250 | 9 | +91 98220 12345 |
| BLK-81002 | 21-10-2025 | Aurangabad | ₹ 86.50 | NA | 09822054321 |
| BLK-81003 | 21-10-2025 | Kolhapoor | 2,400 | 500 | 9822011111 |
| BLK-81004 | 22-10-2025 | NASIK | ₹0 | 0 | 12345 |
After
| Order ID | Order Date | City | Amount | Mins | DQ |
|---|---|---|---|---|---|
| BLK-81001 | 20-Oct-2025 | Pune | 1250.00 | 9 | OK |
| BLK-81002 | 21-Oct-2025 | Sambhaji Nagar | 86.50 | null | Invalid time |
| BLK-81003 | 21-Oct-2025 | Kolhapur | 2400.00 | 500 | Too long |
| BLK-81004 | 22-Oct-2025 | Nashik | 0.00 | 0 | Invalid time |
Cleaning checklist (follow in order):
| ✓ | Step | Command | Section |
|---|---|---|---|
| ☐ | 1. Profile on the entire data set | View › Column quality/distribution/profile | 7.2 |
| ☐ | 2. Remove 2 title rows, promote headers, filter out the Total row | Remove Top Rows, Use First Row as Headers, Text Filter | 7.3 |
| ☐ | 3. Rename Order No → Order ID, Mins → Delivery Time Mins | Double-click header | – |
| ☐ | 4. Clean + Trim all text; UPPERCASE Order ID; Capitalize Each Word City | Transform › Format | 7.6 |
| ☐ | 5. Standardise City with the CityMap merge | Merge Queries (Left Outer) | 7.9 |
| ☐ | 6. Remove ₹, Rs., commas → Fixed Decimal | Replace Values / Text.Remove | 7.8 |
| ☐ | 7. Order Date → Date Using Locale English (India) | Change Type › Using Locale | 7.7 |
| ☐ | 8. Convert UTC rows to IST; add Order Hour | Custom Column | 7.14 |
| ☐ | 9. "NA" → null; Replace Errors | Replace Values, Replace Errors | 7.15 |
| ☐ | 10. Remove duplicates by Order ID + Product ID | Remove Duplicates | 7.4 |
| ☐ | 11. Phone → 10 digits, Phone OK flag | Custom Column | 7.13 |
| ☐ | 12. Data Quality flag column | Conditional Column | 7.16 |
| ☐ | 13. Anti join with Product and DarkStore for unknown codes | Merge (Left Anti) | 7.22 |
| ☐ | 14. Rename steps, group queries, disable load of helpers, check dependencies | Applied Steps, View › Query Dependencies | 6.4, 6.7 |
| ☐ | 15. Close & Apply; check row counts against the source | Home › Close & Apply | 6.11 |
The finished query in the Advanced Editor (shortened) looks like this:
let
Source = Excel.Workbook(File.Contents(FolderPath & "Blinkit_Maharashtra_Export.xlsx"), null, true),
Sheet = Source{[Item = "Orders", Kind = "Sheet"]}[Data],
Skipped = Table.Skip(Sheet, 2),
Promoted = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true]),
NoTotal = Table.SelectRows(Promoted, each Text.StartsWith(Text.Upper(Text.Trim([Order No])), "BLK-")),
Renamed = Table.RenameColumns(NoTotal, {{"Order No", "Order ID"}, {"Mins", "Delivery Time Mins"}}),
TextClean = Table.TransformColumns(Renamed, {
{"Order ID", each Text.Upper(Text.Trim(Text.Clean(_))), type text},
{"City", each Text.Proper(Text.Trim(Text.Clean(_))), type text}}),
CityFix = Table.ExpandTableColumn(
Table.NestedJoin(TextClean, {"City"}, CityMap, {"Raw City"}, "M", JoinKind.LeftOuter),
"M", {"Clean City"}),
CityStd = Table.RemoveColumns(
Table.AddColumn(CityFix, "City Std", each [Clean City] ?? [City], type text),
{"City", "Clean City"}),
Amounts = Table.TransformColumns(CityStd, {{"Amount",
each Number.From(Text.Remove(Text.Replace(Text.From(_), "Rs.", ""), {"₹", ",", " "})), Currency.Type}}),
NAtoNull = Table.ReplaceValue(Amounts, "NA", null, Replacer.ReplaceValue, {"Delivery Time Mins"}),
Typed = Table.TransformColumnTypes(NAtoNull,
{{"Order Date", type date}, {"Delivery Time Mins", Int64.Type}}, "en-IN"),
NoDupes = Table.Distinct(Typed, {"Order ID", "Product ID"}),
DQ = Table.AddColumn(NoDupes, "Data Quality", each
if [Delivery Time Mins] = null or [Delivery Time Mins] <= 0 then "Invalid time"
else if [Delivery Time Mins] > 180 then "Too long" else "OK", type text)
in
DQ
Test with row counts
Aata he bagha: write down the source row count, the count after removing duplicates and the count of flagged rows. If Close & Apply loads a different number, find out why before you build visuals.
Practice task (mini-project)
Repeat the case study for a messy Amazon Now export for Nagpur and Solapur with UTC timestamps. Deliver: a clean Orders query, a CityMap query, two anti-join data-quality queries and a one-line note for each applied step.
Shabbas mitrano! Ha case study purna kela tar tumhi real-world data cleaning sathi tayar aahat.
Ravindra Bagale's Tip
Mitrano, khup students clean the sample file perfectly but never check the totals against the source. After cleaning, compare row counts and total Amount with the original export for one day and one city. If they don't match, find the step that changed them before you build any visuals. Bilkul visru naka.
Thodkyaat sangaycha tar (quick recap)
Nehmi ekach order ne clean kara – junk rows, headers, text cleaning, values, types, duplicates. Pratyek step nantar row count aani total source shi jodun bagha. Samjla ka? Nasel tar case study (7.25) punha ekda kara. Aata pudhe jaauya columns add karayla.