Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

7.1 A Cleaning Workflow You Can Repeat

By Last updated:

Start with a problem

Shahrukh cleans the Pune export in a random order. He changes Amount to a number before removing ₹, and every amount becomes Error. Then he removes duplicates before trimming, so "Kothrud " and "Kothrud" stay as two stores.

Explain simply

Clean in the same order every time: shape, then text hygiene, then types, then blanks and errors, then duplicates, then validity, then restructure, then document. Each stage prepares the next.

Show the data

Before:

Table 1: columns Column1, Column2, Column3, Column4
Column1 Column2 Column3 Column4
Blinkit Pune daily export (fictional practice data)
Generated 08-04-2026 07:00 IST
Order ID Order Date Store Amount
BLK-401 06-04-2026 "Kothrud " ₹1,250
BLK-402 06-04-2026 baner ₹980
BLK-403 07-04-2026 KOTHRUD ₹450
BLK-403 07-04-2026 KOTHRUD ₹450
BLK-404 Baner ₹320
Total ₹3,450

About this data: This table uses columns: Column1, Column2, Column3, Column4. This table uses columns: Rows, Amount sum. This table uses columns: Order ID, Order Date (Date), Store, Amount (Fixed decimal). This table uses columns: Stage, What you do, Section. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_raw_pune_export.csv (source: src/practice/).

Demonstrate

  1. Shape: Remove Top Rows 2 › Use First Row as Headers › filter Order ID does not equal Total.

    Your result should look like this:

    Table 2: columns Rows
    Rows
    5 (BLK-401, 402, 403, 403, 404)

  2. Text: Store › Transform › Format › Trim, then Capitalize Each Word.

    Your result should look like this:

    Table 3: columns Store values
    Store values
    Kothrud, Baner, Kothrud, Kothrud, Baner

  3. Types: Amount › Replace ₹ and , with nothing › Fixed decimal. Order Date › Using Locale › Date, English (India).

    Your result should look like this:

    Table 4: columns Amount sum
    Amount sum
    3,450 (5 rows)

  4. Blanks: Order Date › Replace Values (empty → null) › Fill › Down.

    Your result should look like this:

    Table 5: columns BLK-404 Order Date
    BLK-404 Order Date
    07-Apr-2026

  5. Duplicates: select all columns › Remove Duplicates.

    Your result should look like this:

    Table 6: columns Rows, Amount sum
    Rows Amount sum
    4 3,000

  6. Document: rename steps (6.4).

Show the result

Messy Pune export before and after cleaning, sample layout with fictional data

Your result should look like this (fictional sample):

Table 7: columns Order ID, Order Date (Date), Store, Amount (Fixed decimal)
Order ID Order Date (Date) Store Amount (Fixed decimal)
BLK-401 06-Apr-2026 Kothrud 1,250
BLK-402 06-Apr-2026 Baner 980
BLK-403 07-Apr-2026 Kothrud 450
BLK-404 07-Apr-2026 Baner 320
Total (4 rows) 3,000

Explain what happened

Each stage removes a cause of failure for the next one: no title rows before typing, no ₹ before converting, no stray spaces before de-duplicating.

Explore further

  • Keep the checklist table below as your standard operating procedure.

Detailed reference for this lesson:

Clean in the same order every time. This avoids surprises. Udaharan mhanje, if you remove duplicates before you trim spaces, then "Pune" and "Pune·" are treated as two different values.

Table 8: columns #, Stage, What you do, Section
# Stage What you do Section
1 Profile Turn on the column quality, distribution and profile tools; profile the entire dataset 7.2
2 Shape Remove title rows, promote headers, remove footer/total rows, remove unwanted columns 7.3
3 Text hygiene Trim, Clean, fix case, standardise spellings 7.6, 7.9
4 Types Set data types (use Using Locale for Indian dates); convert ₹ text to numbers 7.7, 7.8
5 Blanks & errors Replace, fill or remove nulls; handle errors 7.5, 7.15
6 Duplicates Remove full-row duplicates or duplicates by key 7.4
7 Validity Flag or filter invalid values and outliers 7.13, 7.16
8 Restructure Split, merge, extract, unpivot, pivot 7.10–7.12, 7.17
9 Combine Append, merge, folder combine, group 7.20–7.23
10 Document Rename steps, check query dependencies, turn off load for helper queries Module 6

Ravindra Bagale's Tip

Friends, the mistake I see most is cleaning in a random order, for example changing types before removing ₹ symbols or before promoting headers. Follow the same order every time: remove junk rows, then promote headers, then trim and clean text, then fix values, then set types, then remove duplicates. A fixed order prevents most conversion errors. Clear?

Fix common mistakes

Table 9: columns Wrong result, Reason, Correction
Wrong result Reason Correction
Every Amount = Error Changed type before removing ₹ and commas Clean text first, then set type
Two Kothrud stores De-duplicated or grouped before Trim/case fix Trim and fix case first
Headers wrong after type change Typed before promoting headers Shape first

Practise independently

Practice task

Put these in the right order: Fill Down, Remove Top Rows, Change Type, Trim.

Hints
  • Shape, then text, then types, then blanks.
Solution

Remove Top Rows, then Trim, then Change Type, then Fill Down (after replacing empty with null).

Think like a professional

A repeatable order makes your queries predictable for teammates – mention your checklist in interviews.

Ravi Sir's Cards for this lesson

A cleaning workflow you can repeat. Cleaning in random order causes errors, like setting types before junk rows are gone.Revision card

मराठी cards