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

By Ravindra Bagale Last updated: 6 October 2026

Start with a problem

Rani sends the Maharashtra export with title rows, a Total row, a duplicate with different case, mixed city spellings, ₹ text, "NA" minutes, a 500-minute delivery and a 5-digit phone. You must deliver a clean table today.

Explain simply

Apply the 7.1 workflow end to end and finish with a Data Quality column. Keep every rule as a named step.

Show the data

Before (after removing title rows):

Table 1: columns Order No, Order Date, City, Amount, Mins, Phone
Order No	Order Date	City	Amount	Mins	Phone
blk-81001	20-10-2025	" pune"	₹1,250	9	+91 90000 00001
BLK-81001	20-10-2025	Pune	₹1,250	9	+91 90000 00001
BLK-81002	21-10-2025	Aurangabad	₹ 86.50	NA	09000000002
BLK-81003	21-10-2025	Kolhapoor	2,400	500	9000000003
BLK-81004	22-10-2025	NASIK	₹0	0	12345
Total			₹4,986.50

About this data: This table uses columns: Order No, Order Date, City, Amount, Mins, Phone. This table uses columns: Rows, Amount sum. This table uses columns: Order ID, Order Date, City, Amount, Mins, Data Quality, Phone OK. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_case_study_blinkit_export.csv, pq_city_map.csv (source: src/practice/).

Demonstrate

Remove Top Rows 2 › Promote headers › Order No filter › Does Not Equal Total › rename Order No → Order ID.

Your result should look like this:

Table 2: columns Rows

Rows

5
Order ID UPPERCASE + Trim; City Trim + Capitalize Each Word; merge CityMap (Nasik→Nashik, Kolhapoor→Kolhapur, Aurangabad→Sambhaji Nagar).

Your result should look like this:

Table 3: columns City values

City values

Pune, Pune, Sambhaji Nagar, Kolhapur, Nashik
Amount: remove ₹, commas, spaces › Fixed decimal. Order Date › Using Locale en-IN. Mins: NA → null › Whole Number.

Your result should look like this:

Table 4: columns Amount sum (5 rows)

Amount sum (5 rows)

4,986.50
Remove Duplicates on Order ID.

Your result should look like this:

Table 5: columns Rows, Amount sum

Rows Amount sum

4 3,736.50
Conditional Column Data Quality: Mins null or ≤ 0 → "Invalid time"; > 180 → "Too long"; else "OK". Phone10 + Phone OK = Text.Length(digits) >= 10.

Your result should look like this:

Table 6: columns Data Quality

Data Quality

OK, Invalid time, Too long, Invalid time

Table 2: columns Rows
Rows
5

Table 3: columns City values
City values
Pune, Pune, Sambhaji Nagar, Kolhapur, Nashik

Table 4: columns Amount sum (5 rows)
Amount sum (5 rows)
4,986.50

Table 5: columns Rows, Amount sum
Rows	Amount sum
4	3,736.50

Table 6: columns Data Quality
Data Quality
OK, Invalid time, Too long, Invalid time

Show the result

Your result should look like this (fictional sample):

Table 7: columns Order ID, Order Date, City, Amount, Mins, Data Quality
Order ID	Order Date	City	Amount	Mins	Data Quality	Phone OK
BLK-81001	20-Oct-2025	Pune	1,250.00	9	OK	TRUE
BLK-81002	21-Oct-2025	Sambhaji Nagar	86.50	null	Invalid time	TRUE
BLK-81003	21-Oct-2025	Kolhapur	2,400.00	500	Too long	TRUE
BLK-81004	22-Oct-2025	Nashik	0.00	0	Invalid time	FALSE
Total			3,736.50

Explain what happened

Upper-casing and trimming before de-duplication made blk-81001 and BLK-81001 the same key, so the ₹1,250 duplicate was removed. Flags keep questionable rows visible.

Explore further

Compare: source Total ₹4,986.50 includes the duplicate ₹1,250 → 4,986.50 − 1,250 = 3,736.50.

Detailed reference for this lesson:

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)

Table 8: columns Order No, Order Date, City, Amount, Mins, Phone
Order No	Order Date	City	Amount	Mins	Phone
blk-81001	20-10-2025	·pune	₹1,250	9	+91 90000 00001
BLK-81001	20-10-2025	Pune	₹1,250	9	+91 90000 00001
BLK-81002	21-10-2025	Aurangabad	₹ 86.50	NA	09000000002
BLK-81003	21-10-2025	Kolhapoor	2,400	500	9000000003
BLK-81004	22-10-2025	NASIK	₹0	0	12345

After

Table 9: columns Order ID, Order Date, City, Amount, Mins, DQ
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):

Table 10: columns ✓, Step, Command, Section
✓	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

In simple words (sopya bhashet)

Friends, a case study is practice in using all the small tools you have learnt, together. Think of it like cleaning a house – first throw out the rubbish (junk rows and the total row), then put things in their place (headers and column names), then dust (Trim, Clean, spellings), and finally count and check everything (types, duplicates, row count and total). If you get the order wrong, you end up doing the work twice. So follow the checklist in order.

Step by step: keep a "control sheet" while you clean

Cleaning is not finished when the table looks clean. It is finished when the numbers match the source and every difference is explained. Keep a small control sheet (on paper or in Excel) and fill one line after each big stage:

Raw export – count the data rows (not the title rows) and note the export's own total if it has one.
After removing junk rows (title rows, blank rows, the Total row) – the row count should drop by exactly the number of junk rows you removed.
After text cleaning (Clean, Trim, UPPER/Proper, CityMap) – the row count must not change. Only the values change.
After removing duplicates – the row count drops. Write down which rows went and why.
After Close & Apply – check the row count in Table view and the total in a card. It must match line 4.

In Power Query, View › Column profile shows the row count. Turn on Column profiling based on entire data set first (7.2). For a quick total, add a temporary Statistics › Sum step on the Amount column. Delete that step afterwards.

Worked example 1 – Panchganga Gul Bhandar, Kolhapur (fictional): the export total that did not match

A fictional Kolhapur jaggery shop exports its October bills. Below the title row and the header row, the sheet has:

Table 11: columns Bill No, Bill Date, City, Item, Amount
Bill No	Bill Date	City	Item	Amount
gb-501	01-10-2025	kolhapur␣	Gul cubes 5 kg	₹450
GB-501	01-10-2025	Kolhapur	Gul cubes 5 kg	₹450
GB-502	01-10-2025	KOLHAPUR	Gul powder 2 kg	Rs. 220
GB-503	02-10-2025	Ichalkaranji	Gul block 10 kg	₹1,050
GB-504	02-10-2025	Sangli	Gul cubes 5 kg	450
Total				2,620

(␣ marks a trailing space.) The control sheet after each stage:

Table 12: columns Stage, Rows, Total Amount, Note
Stage	Rows	Total Amount	Note
Raw data rows (Total row excluded)	5	₹2,620	same as the export's Total row
After Clean / Trim / UPPER on Bill No, Proper on City	5	₹2,620	values changed, row count did not
After Remove Duplicates on Bill No	4	₹2,170	GB-501 was entered twice

The ₹450 difference (2,620 − 2,170) is exactly one GB-501 bill. The shop's export total was counting the double entry. This is the kind of finding you report to the owner: "Your October total is ₹2,170; bill GB-501 was entered twice."

Worked example 2 – Nashik Valley Grapes (fictional exporter): Indian dates and lakh-style amounts

A fictional Nashik grape exporter sends a payment register with dates typed as dd-mm-yyyy and amounts written the Indian way:

Table 13: columns Invoice, Paid On (text), Amount (text)
Invoice	Paid On (text)	Amount (text)
NVG-11	05-01-2026	1,25,000
NVG-12	13-01-2026	₹ 86,400
NVG-13	02-02-2026	Rs.2,10,500

What happens with each approach:

Table 14: columns Approach, 05-01-2026 becomes, 13-01-2026 becomes
Approach	05-01-2026 becomes	13-01-2026 becomes
Change Type › Date on a computer set to English (United States)	1 May 2026 (wrong!)	Error (there is no month 13)
Change Type › Using Locale › Date, English (India) (7.7)	5 January 2026	13 January 2026

For the amounts, remove "Rs.", "₹", spaces and commas first, then change the type to Fixed Decimal Number (7.8): 1,25,000 → 125000, ₹ 86,400 → 86400, Rs.2,10,500 → 210500, total ₹4,21,900. The dangerous one is 05-01-2026: it does not give an error, it silently becomes the wrong month. That is why Using Locale is in the checklist (step 7) and why you check a few dates by eye after the step.

Common mistake – removing duplicates before cleaning the text

In example 1, suppose you click Remove Duplicates on Bill No before Trim and UPPER. Power Query compares text exactly, so "gb-501" and "GB-501" are treated as different values, and both rows stay. The duplicate survives, the total stays ₹2,620, and the report over-counts. Fix: follow the checklist order – Clean, Trim and UPPER first (step 4), Remove Duplicates later (step 10) – and confirm with the control sheet that the row count dropped from 5 to 4.

Test with row counts

Now look at this: 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 Mumbai 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.

Well done, friends! If you have completed this case study, you are ready for real-world data cleaning.

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.

Fix common mistakes

Table 15: columns Wrong result, Reason, Correction
Wrong result	Reason	Correction
5 rows remain	De-duplicated before UPPERCASE/Trim	Clean keys first
Total ₹4,986.50	Duplicate kept	Remove duplicates on Order ID
NASIK not mapped	Mapped before Capitalize (Nasik vs NASIK)	Fix case, then merge

Practise independently

Practice task

Count Data Quality values and the clean total.

Hints

Four rows.

Solution

OK 1, Invalid time 2, Too long 1; total ₹3,736.50.

Think like a professional

End-to-end cleaning with a reconciliation line (source total vs clean total) is an excellent portfolio piece.

Ravi Sir's Cards for this lesson

Case study: cleaning a messy Blinkit export. Real exports have many problems at once. A fixed order solves them cleanly.

Revision card

मराठी cards