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" )
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.
Related articles
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
Related functions
Other functions related to Text.PositionOf are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy