Ravindra BagaleCourses & study guides

8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns

8.8 From Text: Format, Merge Columns, Extract, Parse

By Last updated:

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

  1. Select Drop Location › Add Column › Parse › JSON. A new column shows Record in each cell.
  2. Click the expand icon › tick lat, lng, area › OK.
  3. Set lat/lng to Decimal Number.
  4. Parse › XML works the same way for XML text (common in older courier-partner systems).

Ravindra Bagale's Tip

Ek common chuk mhanje 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. Samjla ka?