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

Text.PositionOf finds where a substring sits inside a text value and returns its position as a number. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to know where a character or word starts inside some text, or pull out everything before or after it, this is the function you start with.

Table of Contents

Syntax of Text.PositionOf Function

Text.PositionOf(text as text, substring as text, optional occurrence as nullable number, optional comparer as nullable function) as any

where

text (required, text). The text value to search inside.
substring (required, text). The value you are looking for within text.
occurrence (optional, nullable number). Which match to report: Occurrence.First, Occurrence.Last, or Occurrence.All. Omit it and you get the first match.
comparer (optional, nullable function). Controls how values are compared, for example case-insensitive matching. Omit it for the default case-sensitive match.

Returns: a number, the 0-based position of the substring (so the first character is position 0). It returns -1 when the substring is not found. With Occurrence.All it returns a list of positions instead of a single number.

In plain terms, you give it some text and a piece to find, and it tells you how far into the text that piece starts.

Example 1: Find where a word starts

Find the position of Planet inside a brand name.

Text.PositionOf("SpreadsheetPlanet", "Planet")

Result: 11

Counting from 0, the P in Planet is the 12th character, which is position 11.

Example 2: Return -1 when the substring is not found

Look for a Q in an invoice number that has none.

Text.PositionOf("INV-2026-0098", "Q")

Result: -1

There is no Q in the text, so the function returns -1 instead of a position. Test for this rather than assuming you always get a real index.

Example 3: Get the last match with Occurrence.Last

The word banana has three a characters. By default you would get the first one, but here you want the last.

Text.PositionOf("banana","a",Occurrence.Last)

Result: 5

Occurrence.Last reports the final a, which sits at position 5.

Example 4: Get every match with Occurrence.All

Now you want all three positions of a, not just one.

Text.PositionOf("banana","a",Occurrence.All)

Result: {1, 3, 5}

With Occurrence.All the function returns a list of every position where a appears. The result is a list now, not a single number, so watch for that downstream.

Example 5: Add a column with the position of a character

Adding the position as a new column is the most common real use of Text.PositionOf.

Say you have a Contacts query with an Email column, and you want the position of the @ sign in each address.

Here is the starting data:

Email
priya@acme.io
sam.lee@data.co
jo@x.org
marketing.team@bigcorp.net

Wrap Text.PositionOf inside Table.AddColumn:

let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    #"Added At Position" = Table.AddColumn(Source, "At Position", each Text.PositionOf([Email], "@"), Int64.Type)
in
    #"Added At Position"

This adds an At Position column holding the @ index for each row.

The result keeps every row and adds the position:

Email	At Position
priya@acme.io	5
sam.lee@data.co	7
jo@x.org	2
marketing.team@bigcorp.net	14

From here you can feed At Position into Text.Start or Text.Range to split the name from the domain.

Things to keep in mind with Text.PositionOf

Positions are 0-based. The first character is 0, not 1. If you pass the result to a worksheet function that expects 1-based positions, add 1.
Matching is case-sensitive by default. Text.PositionOf("Banana","a") finds the lowercase a, not the capital B. Pass Comparer.OrdinalIgnoreCase as the comparer to ignore case.
Occurrence.All returns an empty list when nothing matches. You get {}, not -1, so a missing-match check that works for the single-value form will not work here.
Both inputs must be text. A number throws Expression.Error: We cannot convert the value 42 to type Text. Convert first with Text.From.

Common questions about Text.PositionOf

What is the difference between Text.PositionOf and Text.Contains?

Text.Contains returns true or false for whether the substring is there. Text.PositionOf returns the actual index, or -1 if it is missing.

How do I get the text before or after the match?

Combine it with Text.Start or Text.Range. For the part before the @, use Text.Start([Email], Text.PositionOf([Email], "@")).

List of All Power Query Functions

Related Power Query Functions / Articles: