Text.PositionOfAny

Updated on

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
ArgumentAttributeDescription
TextThe text you want to inspect.
CharactersCharacters to search for in text provided as list.
OccurrenceoptionalThe 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: 7

The 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: 17

And 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
Text.PositionOfAny searches multiple characters in Power Query M

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.

Learn more about Text.PositionOfAny in the following articles:

Other functions related to Text.PositionOfAny are:

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-positionofany