How to use Text.PositionOf in Power BI

In this article you will learn what Text.PositionOf is, learn its syntax, and how to use it.

Text.PositionOf is a Power Query function that returns the position of the specified occurrence of the text value substring found in text. An optional parameter occurrence may be used to specify which occurrence position to return (first occurrence by default). Returns -1 if substring was not found.

Text.PositionOf Syntax

The syntax for Text.PositionOf is below and please note, the ‘occurrence’ and ‘comparer’ parameters are optional.

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

The following built-in comparers are available in the formula language:

• Comparer.Ordinal: Used to perform an exact ordinal comparison
• Comparer.OrdinalIgnoreCase: Used to perform an exact ordinal case-insensitive comparison
• Comparer.FromCulture: Used to perform a culture-aware comparison

Example

We know what Text.PositionOf does and the syntax of it, but how do we use it?

Open the Power Query editor, go to ‘Add Column’ > ‘Custom Column’, and use the above syntax to create a custom column. You can pass a column to the ‘text’ parameter and change the text you want to look for in the ‘substring’.

Below is an example where I have a table that has the text ‘111 (222) 333 (444)’ and I want to find “222”.

Text.PositionOf([Column1], "222")

Using the above formula, we can see that it brings back “5”. This is because “222” starts at position 5.

As Text.PositionOf starts from the first position, we can add an occurrence, so it finds the last occurrence of the text. After the text you want to find, simply add ‘Occurrence.Last’ and this will tell Power Query to find the last occurring text in the string.

I have updated my text column and my formula, which now looks like the below.

Text.PositionOf([Column1], "222", Occurrence.Last)

You can now see the last occurrence of the text I want to find starts at position 20.

Tp learn more, please visit Learn DAX