Text.PositionOf - Text Function

Text.PositionOf is a Power Query M function that finds the position of a specified substring within a text value, with optional parameters for occurrence and comparison type. The function returns the position of the substring or -1 if not found.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
text		The text you want to inspect.
substring		The substring to search for in the text.
occurrence	optional	The Occurrence.Type specifies the occurrence of an element in a sequence. When omitted, Power Query defaults to returning the first match with Occurrence.First. Alternatively, you can return the last (Occurrence.Last) or all matches it finds (Occurrence.All).
comparer	optional	Uses Comparer Functions to determine how values are equated during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

Description

The Text.PositionOf function returns the position of the specific instance of a substring found in the larger text. It defaults to finding the first occurrence. If the substring isn’t found, the function returns -1.

The optional ‘comparer’ parameter lets you control the comparison, offering case-insensitive or culture and locale-aware comparisons.

Examples

Let’s see how to use the function with some practical examples.

Finding the First Occurrence

Imagine a simple string: “We all want pasta”. If you want to find the position of the first occurrence of “a”, you would use the following formula:

Text.PositionOf( "We all want pasta", "a" ) // Returns 3

This indicates that “a” first appears in the 3rd position of the string.

Finding the Position in a Column

Below image shows the function in action. It creates a column called “PositionOf” and finds the position of the letter “a” in different sentences.

Text.PositionOf( [Sentence], "a" )

Text.PositionOf returns the character position in Power Query M

Finding the Last Occurrence

But what if we want to find the last occurrence of “a”? That’s where the Occurrence.Last parameter comes in. Let’s see it in action:

Text.PositionOf( "We all want pasta", "a", Occurrence.Last ) // Returns 16

Finding All Occurrences

If you want to know every position where “a” appears? Just use the Occurrence.All parameter, as in:

Text.PositionOf( "We all want pasta", "a", Occurrence.All ) // Returns { 3, 8, 13, 16 }

These results indicate that “a” appears at the 3rd, 8th, 13th, and 16th positions of the string.

Getting a Specific Occurrence

Another neat trick with Text.PositionOf is that it can return positions from the list of occurrences. For example, to get the second position where “a” appears, we would use:

Text.PositionOf( "We all want pasta", "a", Occurrence.All ) {1} // Returns 8

Case-Sensitive Search

By default, the Text.PositionOf function is case-sensitive. Therefore, searching for “w” in “We all Want pasta” would return -1. However, we can override this using the optional comparer parameter:

Text.PositionOf( 
   "We all Want pasta", 
   "w", 
   Occurrence.First, 
   Comparer.OrdinalIgnoreCase 
)

This example returns 0, as the “w” is the first character in the string. However this is only the case when ignoring capitals.

As previous examples show, you have different options to tailor the Text.PositionOf function to your exact needs. From returning positions for the start or end, showing all and using different comparer functions.

Learn more about Text.PositionOf in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Other functions related to Text.PositionOf are:

Text.PositionOfAny

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-positionof