Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

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

By Last updated:

Start with a problem

Ravindra needs the numeric part of BLK-401, the email domain, and the first two digits of the PIN for a region check.

Explain simply

Extract takes part of a text: Length, First/Last characters, Range, Text Before/After/Between Delimiters.

Show the data

Before:

Order ID Email PIN
BLK-401 ravindra@example.com 411038
BLK-402 shraddha@example.com 422013

About this data: This table uses columns: Order ID, Email, PIN. This table uses columns: Order No, Domain, PIN prefix, PIN length. This table uses columns: Extract option, Input, Output, M function. 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

  1. Add Column › Extract › Text After Delimiter on Order ID › -.

    Your result should look like this:

    Order No
    401
    402

  2. Add Column › Extract › Text After Delimiter on Email › @.

    Your result should look like this:

    Domain
    example.com
    example.com

  3. Add Column › Extract › First Characters on PIN › 2.

    Your result should look like this:

    PIN prefix
    41
    42

  4. Add Column › Extract › Length on PIN to check 6 digits.

Show the result

Your result should look like this (fictional sample):

Order No Domain PIN prefix PIN length
401 example.com 41 6
402 example.com 42 6

Explain what happened

Each extract creates Text.AfterDelimiter, Text.Start or Text.Length in a new column.

Explore further

  • Text Between Delimiters is useful for Blinkit Kothrud (Pune) → Pune.

Detailed reference for this lesson:

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

Fix common mistakes

Wrong result Reason Correction
PIN prefix 41.0 PIN typed as number Keep PIN as Text
Empty result Delimiter not present in that row Check data; use try … otherwise
Extract replaced the column Used Transform tab Use Add Column

Practise independently

Practice task

Extract Last Characters (4) from Phone 9000000002.

Hints
  • Last four digits.
Solution

0002.

Think like a professional

Extracts often become keys; keep them as text and document the rule.

Ravi Sir's Cards for this lesson

Extract: length, first, last, range, between. Codes hide useful parts, like the city in an order ID.Revision card

मराठी cards