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
Friends, many 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 the text case after trimming, not before. This matters for both exams and interviews.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students Trim लावतात आणि data clean झाला असं समजतात, पण web pages आणि apps मधून copy झालेले non-breaking spaces किंवा line breaks Trim काढत नाही. Clean पण वापरा, आणि तरीही value match होत नसेल तर non-breaking space character (#(00A0)) स्पष्टपणे replace करा. Text case trim केल्यानंतर दुरुस्त करा, आधी नाही. हे exam आणि interview दोन्हीसाठी important आहे.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students Trim लगाकर मान लेते हैं कि data clean हो गया, पर web pages और apps से copy हुए non-breaking spaces या line breaks को Trim नहीं हटाता. Clean भी इस्तेमाल करो, और फिर भी value match न हो तो non-breaking space character (#(00A0)) को साफ़ तौर पर replace करो. Text case trim करने के बाद ठीक करो, पहले नहीं. यह exam और interview दोनों के लिए ज़रूरी है.