List.PositionOfAny

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.PositionOfAny

Syntax and Arguments

The syntax for List.PositionOfAny is as follows:

List.PositionOfAny(list as list, values as list, optional occurrence as nullable number) as list

The function takes in three arguments:

1. list (required): the list of text values to search through

2. values (required): the list of characters or strings to search for

3. occurrence (optional): the occurrence of the value to search for (default is 1)

Examples

Let’s take a look at some examples of List.PositionOfAny in action.

Example 1: Finding the Position of a Single Character

Suppose we have a dataset that contains a column of email addresses. We want to create a new column that contains the position of the “@” symbol within each email address.

We can use List.PositionOfAny to do this as follows:

let

Source = Excel.CurrentWorkbook(){[Name=”EmailAddresses”]}[Content],

PositionOfAtSymbol = Table.AddColumn(Source, “PositionOfAtSymbol”, each List.PositionOfAny({[EmailAddress]}, {“@”}))

in

PositionOfAtSymbol

In this example, we are creating a new column called “PositionOfAtSymbol” that uses List.PositionOfAny to find the position of the “@” symbol within each email address in the “EmailAddress” column.

Example 2: Finding the Position of Multiple Characters

Suppose we now want to find the position of both the “@” symbol and the “.” symbol within each email address.

We can use List.PositionOfAny to do this as follows:

let

Source = Excel.CurrentWorkbook(){[Name=”EmailAddresses”]}[Content],

PositionOfAtAndDotSymbols = Table.AddColumn(Source, “PositionOfAtAndDotSymbols”, each List.PositionOfAny({[EmailAddress]}, {“@”, “.”}))

in

PositionOfAtAndDotSymbols

In this example, we are creating a new column called “PositionOfAtAndDotSymbols” that uses List.PositionOfAny to find the position of both the “@” symbol and the “.” symbol within each email address in the “EmailAddress” column.

Example 3: Finding the Position of a Substring

Suppose we have a dataset that contains a column of product descriptions. We want to create a new column that contains the position of the word “size” within each product description.

We can use List.PositionOfAny to do this as follows:

let

Source = Excel.CurrentWorkbook(){[Name=”ProductDescriptions”]}[Content],

PositionOfSize = Table.AddColumn(Source, “PositionOfSize”, each List.PositionOfAny({[ProductDescription]}, {“size”}))

in

PositionOfSize

In this example, we are creating a new column called “PositionOfSize” that uses List.PositionOfAny to find the position of the word “size” within each product description in the “ProductDescription” column.

List.PositionOfAny is a powerful tool in Power Query that can be used to find the position(s) of one or more characters or strings within a given text. By using this function, we can quickly and easily search through large datasets to find specific patterns or values.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)