7. Data Cleaning A–Z in Power Query
7.9 Inconsistent Spellings and Standardisation
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
- Trim and Capitalize Each Word first (7.6), so "KOLHAPUR" becomes "Kolhapur".
- Select City › Transform › Replace Values › Value To Find:
Kolhapoor› Replace With:Kolhapur. - Open Advanced options and tick Match entire cell contents. Otherwise "Nasik" inside "Nasik Road" would also change.
- Repeat for each spelling. Each replacement adds one Applied Step.
Method 2 – Mapping table + Merge (recommended)
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
- Home › Enter Data (in Power BI Desktop) or load CityMap from an Excel sheet. Name it CityMap and turn off Enable load.
- In the Orders/DarkStore query: Home › Merge Queries › select City in the top table and Raw City in CityMap › Join Kind: Left Outer › OK.
- Click the expand icon on the new column › tick only Clean City › untick Use original column name as prefix.
- Add Column › Custom Column › name City Std › formula
if [Clean City] = null then [City] else [Clean City]. - 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, pan nehmi 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
Mitrano, khup 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. Ha niyam lakshat theva.
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.