Text.Range Function in Power Query M (5 Easy Examples)

If you want to pull a piece of text out of a string by its position, the Text.Range function is what you reach for. It returns the part of a text value that starts at a given position and runs for a set number of characters. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Text.Range Function

Text.Range(text as nullable text, offset as number, optional count as nullable number) as nullable text

where

text (required, nullable text). The source string you want to extract characters from.
offset (required, number). The starting position, counted from zero. The first character is at index 0, the second at index 1, and so on.
count (optional, nullable number). How many characters to return from offset onward. Omit it to return everything from offset to the end of the string.

Returns: a nullable text value, the slice of text that begins at offset and runs for count characters. If text is null, it returns null.

In plain terms, you tell it where to start and how many characters you want, and it hands back that chunk of the string.

Example 1: Grab everything from an offset

Pull everything from position 9 to the end of an invoice ID, with no count.

let
    Source = Text.Range("INV-2026-WEST", 9)
in
    Source

Result: WEST

Counting from zero, position 9 is the W in WEST. With no count, the function returns the rest of the string from there.

Example 2: Offset plus count for a fixed slice

Add a count to pull an exact-width chunk out of the middle of the string.

let
    Source = Text.Range("INV-2026-WEST", 4, 4)
in
    Source

Result: 2026

Position 4 is the first 2 in the year. Asking for 4 characters returns 2026.

Example 3: Slice a region code out of every invoice ID

The common real use is running Text.Range down a column with Table.AddColumn.

Say you have an Invoices query with an InvoiceID column, and you want the region code that sits after the year.

Here is the starting data:

InvoiceID
INV-2026-WEST-0481
INV-2026-EAST-0177
INV-2025-SOUTH-309

Add a Region column that grabs 4 characters starting at position 9:

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    Result = Table.AddColumn(Source, "Region", each Text.Range([InvoiceID], 9, 4), type text)
in
    Result

The result keeps every original row and adds the slice:

InvoiceID	Region
INV-2026-WEST-0481	WEST
INV-2026-EAST-0177	EAST
INV-2025-SOUTH-309	SOUT

A fixed offset and count work when the region is exactly four characters. SOUTH is five, so it gets clipped to SOUT. When the length varies, you need to compute the count, which is the next example.

Example 4: Dynamic extraction by position

To handle slices of varying length, compute both offset and count from the positions of two markers with Text.PositionOf.

Say you have a Contacts query with a Listing column, and you want the city that sits inside the parentheses.

Here is the starting data:

Listing
Priya Nair (Mumbai)
Diego Costa (Lisbon)
Hanna Berg (Oslo)

Use the position of ( as the start and the gap between ( and ) as the count:

let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    Result = Table.AddColumn(Source, "City", each Text.Range([Listing], Text.PositionOf([Listing], "(") + 1, Text.PositionOf([Listing], ")") - Text.PositionOf([Listing], "(") - 1), type text)
in
    Result

The result pulls the right city no matter how long it is:

Listing	City
Priya Nair (Mumbai)	Mumbai
Diego Costa (Lisbon)	Lisbon
Hanna Berg (Oslo)	Oslo

The + 1 moves past the opening (, and subtracting the two positions then taking off 1 more gives the exact character count between the parentheses.

Example 5: Guard an out-of-range count with try otherwise

Text.Range errors when you ask for more characters than the string has. A call like Text.Range("Q3", 1, 5) reads from position 1 but only one character remains, so it throws Expression.Error: The 'count' argument is out of range. rather than returning what it can.

When the source might be shorter than expected, wrap the call in try ... otherwise to supply a fallback:

let
    Source = try Text.Range("Q3", 1, 5) otherwise "too short"
in
    Source

Result: too short

The over-long count would normally error, but try ... otherwise catches it and returns too short instead.

Things to keep in mind with Text.Range

It errors when there aren’t enough characters. A count past the end of the string throws Expression.Error: The 'count' argument is out of range. Compute an exact count or wrap the call in try ... otherwise (Example 5).
The offset is zero-based. The first character is at position 0, not 1. If you are coming from the worksheet MID function, subtract one from the start you would use there.
null text returns null. A blank-prone column gives back null, not an empty string. Guard it with Text.Range([Column] ?? "", 0, 3) if you need text. The same null behavior shows up in Text.Contains.
It returns text only. Slicing a number-like value such as 2026 still gives you the text "2026". Wrap the result in Number.FromText if you need a number.
Trim before you slice if positions can drift. Stray leading spaces shift every offset, so clean the column with Text.Trim first to keep your positions reliable.

Performance and query folding

Text.Range is a local text operation. Against most sources it does not fold to the source query, so it runs on your machine after the data loads. For the per-row extraction shown above, that is fine even on tables with thousands of rows.

Common questions about Text.Range

What is the difference between Text.Range and Text.Middle?

Both pull a slice from a starting position, but Text.Range errors when the count runs past the end of the string, while Text.Middle returns whatever characters are available. Reach for Text.Range when you want it to fail loudly on bad data, and Text.Middle when you want it to take what it can get.

How is Text.Range different from Text.Start and Text.End?

Text.Start takes characters from the beginning and Text.End takes them from the end, so neither needs a position. Text.Range starts at any offset you give it, which is what you want when the slice sits in the middle of the string.

List of All Power Query Functions

Related Power Query Functions / Articles: