List.PositionOf

D

Back to top

T

Back to top

#

Back to top

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

Understanding the List.PositionOf function

The List.PositionOf function is used to find the position of an item in a list. This function takes two arguments: the list to search and the item to find. The syntax for this function is as follows:

List.PositionOf(list as list, item as any) as nullable number

The function returns the position of the item in the list as a nullable number. If the item is not found in the list, the function returns null.

The M code behind List.PositionOf

The M code behind the List.PositionOf function is relatively simple. The function first checks if the list is null. If the list is null, the function returns null. If the list is not null, the function uses the List.Generate function to loop through the list and find the position of the item.

Here is the M code behind the List.PositionOf function:

(list as list, item as any) =>

if list = null then null

else List.Generate(

() => [i = 0, found = false],

each [i] <= List.Count(list) - 1 and not [found],

each [i = [i] + 1, found = list{i} = item],

each [i]

)

The List.Generate function is used to generate a list of values based on a set of rules. In this case, the function generates a list of values that increment the index of the list until the item is found. The function then returns the index of the item in the list.

Examples of using List.PositionOf

Here are a few examples of using the List.PositionOf function:

Example 1

Suppose we have a list of numbers and we want to find the position of the number 5 in the list. Here is the M code to achieve this:

let

list = {1, 2, 3, 4, 5, 6},

position = List.PositionOf(list, 5)

in

position

The result of this code is 4, which is the position of 5 in the list.

Example 2

Suppose we have a list of names and we want to find the position of a specific name in the list. Here is the M code to achieve this:

let

list = {“John”, “Mary”, “Bob”, “Sarah”},

position = List.PositionOf(list, “Bob”)

in

position

The result of this code is 2, which is the position of "Bob" in the list.

Example 3

Suppose we have a list of records and we want to find the position of a specific record in the list. Here is the M code to achieve this:

let

list = {

[Name=”John”, Age=25],

[Name=”Mary”, Age=30],

[Name=”Bob”, Age=40],

[Name=”Sarah”, Age=35]

},

position = List.PositionOf(list, [Name=”Bob”, Age=40])

in

position

The result of this code is 2, which is the position of the record with the name "Bob" and age 40 in the list.

The List.PositionOf function is a useful function in Power Query that allows you to find the position of an item in a list. The M code behind this function is relatively simple but provides a powerful way to search for items in a list. By understanding how this function works, you can use it to manipulate and transform data more effectively in Power Query.

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)