7. Data Cleaning A–Z in Power Query
7.6 Extra Spaces, Non-printable Characters and Text Case
Before
| City | Area | Customer |
|---|---|---|
| ·Pune | kothrud | ruhi bagale |
| PUNE·· | KOTHRUD | RUHI BAGALE |
| pune | Kothrud↵ | Ruhi Bagale |
| Pune | baner | shahrukh |
After
| City | Area | Customer |
|---|---|---|
| Pune | Kothrud | Ruhi Bagale |
| Pune | Kothrud | Ruhi Bagale |
| Pune | Kothrud | Ruhi Bagale |
| Pune | Baner | Shahrukh |
(· = an extra space, ↵ = a hidden line-break character copied from another system.)
| Command (Transform › Format) | What it does | Example |
|---|---|---|
| Trim | Removes spaces at the start and end only | "·Pune··" → "Pune" |
| Clean | Removes non-printable control characters (line feeds, tabs etc.) | "Kothrud↵" → "Kothrud" |
| lowercase | all small letters | "PUNE" → "pune" |
| UPPERCASE | ALL CAPITALS (good for codes) | "blk-001" → "BLK-001" |
| Capitalize Each Word | First letter of each word capital | "ruhi bagale" → "Ruhi Bagale" |
| Add Prefix / Add Suffix | Adds fixed text | "10001" → "BLK-10001" |
Steps in Power BI
- Select the text columns (Ctrl + click City, Area, Customer).
- Transform › Format › Clean, then Transform › Format › Trim.
- Transform › Format › Capitalize Each Word (or UPPERCASE for code columns such as Store ID, Product ID).
- To fix double spaces inside text ("RUHI··BAGALE"): Transform › Replace Values › find two spaces › replace with one space. Repeat until none are left, or use the M code below.
- To remove non-breaking spaces (common in web copy-paste): Replace Values › Advanced options › Replace using special characters › Insert special character › Non-breaking space → replace with a normal space.
Cleaned = Table.TransformColumns(Source, {
{"City", each Text.Proper(Text.Trim(Text.Clean(_))), type text},
{"Store ID", each Text.Upper(Text.Trim(_)), type text},
// collapse multiple inner spaces into one
{"Customer", each Text.Combine(List.Select(Text.Split(Text.Trim(_), " "), each _ <> ""), " "), type text}
})
Power Query is case-sensitive, the data model is not
In Power Query "pune" and "Pune" are different values. After loading, the Power BI model (VertiPaq) treats text case-insensitively, so it may show fakt one spelling for both, whichever it meets first. Fix case in Power Query so that the value shown is the one you want.
Tip
Trim never removes spaces in the middle of text. That is why "Hotgi··Road" survives a Trim.
Practice task
Clean a Customer table where names come as "zoya", "ZOYA ", " Zoya" and e-mails in mixed case. Names → Capitalize Each Word; e-mails → lowercase; all columns trimmed and cleaned.
Ravindra Bagale's Tip
Mitrano, khup students apply Trim and think the data is clean, but Trim does not remove non-breaking spaces or line breaks copied from web pages and apps. Use Clean as well, and if a value still doesn't match, replace the non-breaking space character (#(00A0)) explicitly. Fix text case after trimming, not before. He exam aani interview doghansathi important aahe.