Text.PositionOf

Updated on

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
ArgumentAttributeDescription
textThe text you want to inspect.
substringThe substring to search for in the text.
occurrenceoptionalThe 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).
compareroptionalUses 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 function primarily 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.

Imagine a simple string: “We all want pasta”. If we want to find the position of the first occurrence of “a”, we 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. 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

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

What if we 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.

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

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:

Other functions related to Text.PositionOf are:

BI Gorilla Youtube Channel

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