7.25 Case Study: Cleaning a Messy Blinkit Export End to End

By Ravindra Bagale Last updated: 25 September 2026

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

Friends, many students clean the sample file perfectly but never check the totals against the source. After cleaning, compare the 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. Never forget this.

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.