Text.PositionOf Function in Power Query

The Text.PositionOf function returns the index position of the of the specified occurrence of the text value substring found in text value. 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.

Syntax

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

Example: Returns the index position of the first occurrence of the given text.

Power Query M

let
  Source = Text.PositionOf("Hello, my name is Ashish! Ashish is a good boy.", "Ashish")
in
  Source

The output of the above code is 18.

Example: Returns the index position of the last occurrence of the given text.

Power Query M

let
  Source = Text.PositionOf("Hello, my name is Ashish! Ashish is a good boy.", "Ashish", Occurrence.Last)
in
  Source

The output of the above code is 26.

Example: Returns the index positions as a list of the all the occurrences of the given text.

Power Query M

let
  Source = Text.PositionOf("Hello, my name is Ashish! Ashish is a good boy.", "Ashish", Occurrence.All)
in
  Source

The output of the above code is shown below:

Previous Next