Text.PositionOf
This function retrieves the position of a specified text value substring within a larger text. An optional parameter, "occurrence," can be included to specify which occurrence position to return (with...
Text.PositionOf(text as text, substring as text, optional occurrence as nullable number, optional comparer as nullable function) as anyThis function retrieves the position of a specified text value substring within a larger text. An optional parameter, "occurrence," can be included to specify which occurrence position to return (with the default being the first occurrence). If the substring is not found, the function returns -1.
The "comparer" parameter is used to control the comparison method. Comparers enable case-insensitive or culture/ locale-aware comparisons. In the formula language, the following built-in comparers are available:
- Comparer.Ordinal: Exact ordinal comparison
- Comparer.OrdinalIgnoreCase: Exact ordinal case-insensitive comparison
- Comparer.FromCulture: Culture-aware comparison.
Retrieve the position of the initial instance of the term "World" within the text "Hello, World! Hello, World!" using the Power Query MText function.
The output will be 7.
Acquire the position of the final occurrence of the term "World" in the text "Hello, World! Hello, World!" by applying the Power Query MText function.
The output will be 21.