Ravindra BagaleCourses & study guides

7. Data Cleaning A–Z in Power Query

7.12 Extract: Length, First/Last Characters, Range and Delimiters

By Last updated:

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

  1. Select Store ID.
  2. Add Column › Extract › Range.
  3. Starting Index: 4, Number of Characters: 3 › OK. (Power Query counts from 0, so index 4 is the 5th character.)
  4. Rename the new column to City Code.
  5. 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

Remember one thing: 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. After extracting, always scroll through a sample of rows, not just the first five. Clear?

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).