Text.PositionOfAny is a Power Query M function that finds the first position of any character in a specified list within a text value. The function returns the position of the first found character, with an optional parameter to specify the occurrence.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Text.PositionOfAny(
text as text,
characters as list,
optional occurrence as nullable number,
) as any| Argument | Attribute | Description |
|---|---|---|
| Text | The text you want to inspect. | |
| Characters | Characters to search for in text provided as list. | |
| 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). |
Description
The Text.PositionOfAny functions finds the location of any character from a designated list of characters that appears within a specific text string. It can be tailored with an optional parameter, ‘occurrence’, to determine the position of the specific instance to return.
Examples
To get a firm grasp on the workings of the Text.PositionOfAny function, let’s dive into some practical examples.
Consider this phrase: “You like guacamole”. Let’s say you’re intrigued to pinpoint the position of the first appearance of the letter “e”. You can use this formula to help:
Text.PositionOfAny( "You like guacamole", { "e" } ) // Output: 7The outcome shows that the first “e” appears at the seventh index position in the string.
You might find the Text.PositionOf function quite similar to the Text.PositionOf function at this point. However, the true advantage of Text.PositionOfAny becomes apparent when we search for multiple values simultaneously.
Suppose you want to determine the position of the first occurrence of either the letter “e” or “u”. The Text.PositionOfAny function comes in handy for such requirements, as illustrated below:
Text.PositionOfAny( "You like guacamole", { "e", "u" } ) // Output: 2
/* -------- this is identical to -------- */
Text.PositionOfAny( "You like guacamole", { "e", "u" }, Occurrence.First )From the searched characters, the letter “u” is found at index position 2. By default the function applies the Occurrence.First enumeration to search for the first match.
But what if you’re interested in finding the first appearance of these characters starting from the end of the string? This is when you’d use the Occurrence.Last enumeration:
Text.PositionOfAny( "You like guacamole", { "e", "u" }, Occurrence.Last ) // Output: 17And what if we need to find all instances of the letters “e” and “u”? The Occurrence.All enumeration solves this task for us:
// Output: { 2, 7, 10, 17 }
Text.PositionOfAny( "You like guacamole", { "e", "u" }, Occurrence.All ) This indicates that “e” and “u” can be found at the 2nd, 7th, 10th, 13th, and 17th positions of the phrase.
To apply this to a column named ‘Sentence‘ and display the positions of “e” and “u”, you could visualize it like this:
let
ListOfPositions = Text.PositionOfAny( [Sentence], {"e", "u"}, Occurrence.All ),
ListOfPositionsAsText = List.Transform( ListOfPositions, each Text.From( _ ) ),
PositionsAsText = Text.Combine( ListOfPositionsAsText, ", " )
in
PositionsAsText
The code above changes the list of number positions into a list of text values. The Text.Combine function then merges these text values into a single cell separated by a comma.
These examples clearly show that the Text.PositionOfAny function is versatile when it comes to finding the positions of various characters in a string. To use the function to its fullest, be sure to use the Occurrence.Type parameter to get the results you need.
Related articles
Learn more about Text.PositionOfAny 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.PositionOfAny are:
