7. Data Cleaning A–Z in Power Query
7.6 Extra Spaces, Non-printable Characters and Text Case
Start with a problem
A Store chart shows four bars: "Kothrud ", Kothrud in capitals, baner and Baner. There are only two stores.
Explain simply
Trim removes spaces at the start and end. Clean removes non-printable characters. Format changes case (lowercase, UPPERCASE, Capitalize Each Word).
Show the data
Before:
| Store | Distinct? |
|---|---|
| "Kothrud " | 1 |
| baner | 2 |
| KOTHRUD | 3 |
| Baner | 4 |
About this data: This table uses columns: Store, Distinct?. This table uses columns: Store, Rows (5 order rows). This table uses columns: City, Area, Customer. 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
- Tick View › Show whitespace to see the trailing space.
-
Select Store › Transform › Format › Trim, then Format › Clean.
Your result should look like this:
Store Kothrud baner KOTHRUD Baner -
Transform › Format › Capitalize Each Word.
Your result should look like this:
Store Kothrud baner, then Baner KOTHRUD, then Kothrud Baner -
Check Column distribution: distinct count.
Show the result
Your result should look like this (fictional sample):
| Store | Rows (5 order rows) |
|---|---|
| Kothrud | 3 (401, 403, 403) |
| Baner | 2 (402, 404) |
| Distinct values | 2 |
Explain what happened
Power BI treats text as different if spaces or case differ (for grouping in Power Query; DAX is case-insensitive but keeps the first spelling). Trim + Capitalize makes one spelling per store.
Explore further
- Use UPPERCASE for codes (Order ID) and Capitalize Each Word for names.
Detailed reference for this lesson:
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··", then "Pune" |
| Clean | Removes non-printable control characters (line feeds, tabs etc.) | "Kothrud↵", then "Kothrud" |
| lowercase | all small letters | "PUNE", then "pune" |
| UPPERCASE | ALL CAPITALS (good for codes) | "blk-001", then "BLK-001" |
| Capitalize Each Word | First letter of each word capital | "ruhi bagale", then "Ruhi Bagale" |
| Add Prefix / Add Suffix | Adds fixed text | "10001", then "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 "Andheri··East" survives a Trim.
Practice task
Clean a Customer table where names come as "zoya", "ZOYA ", " Zoya" and e-mails in mixed case. Names, then Capitalize Each Word; e-mails, then 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 दोनों के लिए ज़रूरी है.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Still 3 values | Clean/Trim skipped non-breaking spaces (char 160) | Replace Values: non-breaking space, then normal space, then Trim |
| Order IDs became Blk-401 | Capitalize Each Word on codes | Use UPPERCASE for codes |
| Merge with stores table fails | Keys cleaned on one side only | Clean both sides |
Practise independently
Practice task
How many distinct Store values are there before and after cleaning?
Hints
- Count spellings.
Solution
Before: 4; after: 2 (Kothrud, Baner).
Think like a professional
Text hygiene before joins and grouping prevents most 'missing data' tickets.
Revision card
Comparison card