Text.PositionOfAny

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 function locates the position of any character from a specified list within a given text string. You can customize its behavior with an optional ‘occurrence’ parameter to find the first, last, or all instances of the characters.

Examples

To understand how the Text.PositionOfAny function works, let’s look at some practical examples.

Finding a Single Character

Consider this phrase: “You like guacamole”. Let’s say you want to find the first appearance of the letter “e”. You can use this formula:

Text.PositionOfAny( "You like guacamole", { "e" } ) // Output: 7

This shows that the first “e” appears at the seventh position in the string.

Finding Multiple Characters

At first, Text.PositionOfAny might seem similar to Text.PositionOf. But its real power shows when you search for multiple characters at once.

Suppose you want to find the first occurrence of either the letter “e” or “u”. Here’s how Text.PositionOfAny can help:

Text.PositionOfAny( "You like guacamole", { "e", "u" } ) // Output: 2

In this case, the letter “u” is found first, at the second position. By default, the function looks for the first match. This code is the same as:

Text.PositionOfAny( "You like guacamole", { "e", "u" }, Occurrence.First ) // Output: 2

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.

Searching from the End of the String

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

This shows that the last occurrence of either “e” or “u” is at the 17th position.

Finding All Instances

And what if we need to find all instances of the letters “e” and “u”? The Occurrence.All enumeration does this for you:

// Output: { 2, 7, 10, 17 }
Text.PositionOfAny( "You like guacamole", { "e", "u" }, Occurrence.All ) 

This means that “e” and “u” are found at the 2nd, 7th, 10th, and 17th positions in the phrase.

Applying to a Column

To apply this to a column named ‘Sentence’ and display the positions of “e” and “u”, you can use the following code:

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 commas.

These examples show that the Text.PositionOfAny function is useful for finding the positions of various characters in a string.

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:

• Text.PositionOf

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