8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.8 From Text: Format, Merge Columns, Extract, Parse
Start with a problem
Ravindra needs upper-case Order IDs, an email domain column, and an address line – without losing originals.
Explain simply
Add Column › From Text has Format (case, trim, prefix/suffix), Merge Columns, Extract and Parse (JSON/XML).
Show the data
| Order ID | Area | City | |
|---|---|---|---|
| blk-401 | ravindra@example.com | Kothrud | Pune |
| blk-402 | shraddha@example.com | Gangapur | Nashik |
About this data: This table uses columns: Order ID, Email, Area, City. This table uses columns: UPPERCASE, Domain, Address. This table uses columns: Command, Options, Example on our data. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: pq_customers_text.csv (source: src/practice/).
Demonstrate
-
Order ID › Add Column › Format › UPPERCASE.
Your result should look like this:
UPPERCASE BLK-401, BLK-402 -
Email › Extract › Text After Delimiter
@.Your result should look like this:
Text After Delimiter example.com -
Area + City › Merge Columns
,.Your result should look like this:
Merged Kothrud, Pune; Gangapur, Nashik -
Format › Add Prefix
IN-on City to see prefix/suffix.
Show the result
Your result should look like this (fictional sample):
| UPPERCASE | Domain | Address |
|---|---|---|
| BLK-401 | example.com | Kothrud, Pune |
| BLK-402 | example.com | Gangapur, Nashik |
Explain what happened
Each command generated a Text function in a new column.
Explore further
- Parse › JSON turns a JSON text column into a record you can expand.
Detailed reference for this lesson:
| Command | Options | Example on our data |
|---|---|---|
| Format | lowercase, UPPERCASE, Capitalize Each Word, Trim, Clean, Add Prefix, Add Suffix | Add Prefix "₹ " to show a text label; UPPERCASE City Code |
| Merge Columns | Separator, new column name | Store Location = Area, City (Module 7.11) |
| Extract | Length, First/Last Characters, Range, Text Before/After/Between Delimiters | Email Domain = text after "@" (Module 7.12) |
| Parse | JSON, XML | Turn a JSON text column into a record you can expand |
Parse (JSON) – worked example. A Blinkit app export stores the drop location as JSON text in a column called Drop Location: {"lat":18.5074,"lng":73.8077,"area":"Kothrud"}.
Steps in Power BI
- Select Drop Location › Add Column › Parse › JSON. A new column shows Record in each cell.
- Click the expand icon › tick
lat,lng,area› OK. - Set lat/lng to Decimal Number.
- Parse › XML works the same way for XML text (common in older courier-partner systems).
Ravindra Bagale's Tip
A common mistake is using Format › Capitalize Each Word on names like "McDonald" or codes like "BLK-PUN-01", which then look wrong. Apply case changes only to columns where they make sense, and keep codes in uppercase. Clear?
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे "McDonald" सारख्या नावांवर किंवा "BLK-PUN-01" सारख्या codes वर Format › Capitalize Each Word वापरणं, मग ते चुकीचे दिसतात. Case बदल फक्त ज्या columns मध्ये अर्थपूर्ण आहे तिथेच करा, आणि codes uppercase मध्ये ठेवा. समजलं का?
Ravindra Bagale's Tip – हिंदी
एक common गलती है "McDonald" जैसे नामों या "BLK-PUN-01" जैसे codes पर Format › Capitalize Each Word लगाना, जिससे वे गलत दिखते हैं. Case बदलाव सिर्फ़ उन्हीं columns पर करो जहाँ उसका मतलब हो, और codes को uppercase में रखो. समझ आया?
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Original Order ID lost | Used Transform | Add Column |
| Domain empty | No @ | Validate first (7.13) |
| Merged shows null | Old version / custom formula | Use Merge Columns, which skips nulls |
Practise independently
Practice task
Add Suffix (MH) to City for C02.
Hints
- Suffix adds at end.
Solution
"Nashik (MH)".
Think like a professional
Keep display formatting out of keys; format in a separate column.
Revision card