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

7. Data Cleaning A–Z in Power Query

7.9 Inconsistent Spellings and Standardisation

By Last updated:

Start with a problem

A City chart shows Pune, pune, PUNE, Nasik, Nashik, Kolhapoor and Kolhapur – seven bars for three cities.

Explain simply

Fix case and spaces first (Trim + Capitalize Each Word). Then map the remaining spelling variants with a small mapping table (Raw City → Clean City) using a Left Outer merge.

Show the data

Before:

Order ID City Amount
601 Pune 100
602 pune 200
603 "PUNE " 150
604 Nasik 300
605 Nashik 250
606 Kolhapoor 400
607 Kolhapur 350

About this data: This table uses columns: Order ID, City, Amount. This table uses columns: Raw City, Clean City. This table uses columns: Order, City, Clean City. This table uses columns: City Std, Orders, Amount (₹). Values shown are the lesson examples already on this page (not a new dataset).

Mapping table (pq_city_map.csv):

Raw City Clean City
Nasik Nashik
Kolhapoor Kolhapur
Aurangabad Sambhaji Nagar

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

Demonstrate

  1. City › Trim, then Capitalize Each Word.

    Your result should look like this:

    Distinct City values
    Pune, Nasik, Nashik, Kolhapoor, Kolhapur (5)

  2. Home › Merge Queries › City = CityMap[Raw City] › Left Outer › expand Clean City.

    Your result should look like this:

    Order City Clean City
    604 Nasik Nashik
    606 Kolhapoor Kolhapur
    601 Pune null

  3. Add Column › Custom Column City Std = [Clean City] ?? [City]; remove City and Clean City.

    Your result should look like this:

    Distinct City Std
    Pune, Nashik, Kolhapur (3)

Show the result

Your result should look like this (fictional sample):

City Std Orders Amount (₹)
Pune 3 450
Nashik 2 550
Kolhapur 2 750
Total 7 1,750

Explain what happened

Case/space fixes collapsed 7 spellings to 5; the mapping table fixed the last two variants. ?? keeps the original when there is no mapping.

Explore further

  • A mapping table is easier to maintain than a long chain of Replace Values steps.

Detailed reference for this lesson:

The same city arrives with many spellings. Some are typos, and some are the older official name.

Before

Store ID City (raw)
BLK-SAM-01 Aurangabad
BLK-SAM-02 Chh. Sambhaji Nagar
AMZ-SAM-01 Sambhajinagar
BLK-KOP-01 Kolhapoor
BLK-KOP-02 Kolapur
AMZ-KOP-01 KOLHAPUR
BLK-NSK-01 Nasik

After

Store ID City
BLK-SAM-01 Sambhaji Nagar
BLK-SAM-02 Sambhaji Nagar
AMZ-SAM-01 Sambhaji Nagar
BLK-KOP-01 Kolhapur
BLK-KOP-02 Kolhapur
AMZ-KOP-01 Kolhapur
BLK-NSK-01 Nashik

Method 1 – Replace Values (few variations)

Steps in Power BI

  1. Trim and Capitalize Each Word first (7.6), so "KOLHAPUR" becomes "Kolhapur".
  2. Select City › Transform › Replace Values › Value To Find: Kolhapoor › Replace With: Kolhapur.
  3. Open Advanced options and tick Match entire cell contents. Otherwise "Nasik" inside "Nasik Road" would also change.
  4. Repeat for each spelling. Each replacement adds one Applied Step.

Keep all spellings in one small table, CityMap, that anyone can update:

Raw City Clean City
Aurangabad Sambhaji Nagar
Chh. Sambhaji Nagar Sambhaji Nagar
Chhatrapati Sambhaji Nagar Sambhaji Nagar
Sambhajinagar Sambhaji Nagar
Kolhapoor Kolhapur
Kolapur Kolhapur
Nasik Nashik

Steps in Power BI

  1. Home › Enter Data (in Power BI Desktop) or load CityMap from an Excel sheet. Name it CityMap and turn off Enable load.
  2. In the Orders/DarkStore query: Home › Merge Queries › select City in the top table and Raw City in CityMap › Join Kind: Left Outer › OK.
  3. Click the expand icon on the new column › tick only Clean City › untick Use original column name as prefix.
  4. Add Column › Custom Column › name City Std › formula if [Clean City] = null then [City] else [Clean City].
  5. Remove the old City and Clean City columns and rename City Std to City.
Merged   = Table.NestedJoin(Trimmed, {"City"}, CityMap, {"Raw City"}, "Map", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Map", {"Clean City"}),
Std      = Table.AddColumn(Expanded, "City Std", each [Clean City] ?? [City], type text)

(The ?? operator means "use the left value, or the right value if the left one is null".)

Method 3 – Fuzzy matching

In the Merge dialog, tick Use fuzzy matching to perform the merge and open Fuzzy matching options (Similarity threshold 0–1, Ignore case, Match by combining text parts, Maximum number of matches, and an optional Transformation table). Fuzzy matching catches "Kolhapoor" automatically. But always review the result: a low threshold can wrongly match "Nashik" with "Nagpur".

Ekdum practical topic aahe – store staff "Kolapur", "kolhapur ", "KOLHAPUR" ase kahihi lihitat. Mapping table ha pakka upay aahe.

Ravindra Bagale's Tip

Friends, many students add a 15th Replace Values step instead of switching to a mapping table, or run Replace Values without Match entire cell contents and turn "Pune Station" into a mess. Trim first so " Kolapur" matches. When there are more than a handful of variants, keep a small mapping table (Wrong → Correct) and merge it. Remember this rule.

Practice task

Build a AreaMap table that maps "Hinjawadi", "Hinjewadi Phase 1" and "HINJEWADI" to Hinjewadi, and "Gangapur Rd" to Gangapur Road. Merge it with DarkStore using Method 2.

Fix common mistakes

Wrong result Reason Correction
Nasik not mapped Merge ran before Trim/case fix (keys differ) Clean first, then merge
Row count increased after merge Duplicate Raw City in mapping table Remove duplicates in the map
Pune became null Expanded Clean City without fallback Use [Clean City] ?? [City]

Practise independently

Practice task

What is Nashik's total after standardisation?

Hints
  • 604 + 605.
Solution

₹550 (300 + 250).

Think like a professional

Ask the business to own the mapping table – then new variants are fixed without editing queries.

Ravi Sir's Cards for this lesson

Inconsistent spellings and standardisation. One city with 3 spellings splits its sales into 3 bars.Revision card

मराठी cards