7. Data Cleaning A–Z in Power Query
7.12 Extract: Length, First/Last Characters, Range and Delimiters
Transform › Extract replaces the column; Add Column › Extract creates a new one. Examples on our data:
| Extract option | Input | Output | M function |
|---|---|---|---|
| Length | Kolhapuri Misal Masala | 22 | Text.Length |
| First Characters (3) | BLK-10001 | BLK | Text.Start([Order ID], 3) |
| Last Characters (5) | BLK-10001 | 10001 | Text.End([Order ID], 5) |
| Range (start 4, 3 chars) | BLK-PUN-KOT-01 | PUN | Text.Middle([Store ID], 4, 3) |
| Text Before Delimiter "@" | shraddha.bagale@example.com | shraddha.bagale | Text.BeforeDelimiter |
| Text After Delimiter "@" | shraddha.bagale@example.com | example.com | Text.AfterDelimiter |
| Text Between Delimiters "(" ")" | Blinkit Baner (Pune) | Pune | Text.BetweenDelimiters |
Steps in Power BI – extract City Code from Store ID
- Select Store ID.
- Add Column › Extract › Range.
- Starting Index:
4, Number of Characters:3› OK. (Power Query counts from 0, so index 4 is the 5th character.) - Rename the new column to City Code.
- For delimiters, open Advanced options to choose Scan for the delimiter From the start / From the end and how many delimiters to skip (for example the text after the last "-").
AddCode = Table.AddColumn(Source, "City Code", each Text.Middle([Store ID], 4, 3), type text),
LastPart = Table.AddColumn(AddCode, "Store No", each Text.AfterDelimiter([Store ID], "-", {0, RelativePosition.FromEnd}), type text)
Ravindra Bagale's Tip
Ek goshta lakshat theva: using Range or First Characters on codes whose length varies ("BLK-1" and "BLK-10001") gives wrong results on some rows. Delimiter-based extraction (Text Before/After Delimiter) is more robust. Nehmi scroll through a sample of rows, not just the first five, after extracting. Samjla ka?
Practice task
From Customer e-mails, extract the domain (Text After Delimiter "@"). From Product Name "Amul Butter 100 g", extract the text after the last space (the unit).