List.Positions

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will take a closer look at the M code behind the List.Positions function and how it can be used to simplify your data analysis.

What is the List.Positions function?

The List.Positions function is a built-in function in Power Query that allows you to find all the occurrences of a specific value in a list. The function takes two arguments: the list to search and the value to find.

The List.Positions function returns a list of positions where the value is found in the list. If the value is not found in the list, the function returns an empty list.

How to use the List.Positions function

Using the List.Positions function is straightforward. Let’s say you have a list of numbers and you want to find all the positions where the number 5 appears. Here’s how you can use the List.Positions function to achieve that:

let

numbers = {1, 3, 5, 2, 5, 4, 5},

positions = List.Positions(numbers, 5)

in

positions

This will return the following list of positions:

{2, 5, 7}

The M code behind the List.Positions function

The M code behind the List.Positions function is quite simple. Here's the code:

(List as list, Value as any) as list =>

let

Source = List.Generate(

() => [i = 0, res = {}],

each [i] < List.Count(List),

each [i = [i] + 1, res = if List{[i]} = Value then res & {[i]} else res],

each [res]

)

in

Source{0}

Let's break down the code and see how it works.

The first line defines the function signature. The function takes two arguments: a list and a value. The function returns a list of positions where the value is found in the list.

The second line starts the function body with a let expression. Inside the let expression, we define a variable called "Source" which holds the result of our function.

The third line uses the List.Generate function to generate a list of positions where the value is found in the list. The List.Generate function takes four arguments:

1. An initial state of the generator function. In this case, we initialize the generator function with a counter (i) set to 0 and an empty list (res).

2. A condition that stops the generator function. In this case, we stop the generator function when the counter (i) is equal to the length of the list.

3. A function that generates the next state of the generator function. In this case, we increment the counter (i) by 1 and add the position to the list (res) if the value at that position equals the value we're looking for.

4. A function that returns the final result of the generator function. In this case, we return the list of positions where the value is found in the list.

The fourth line returns the first element of the "Source" list, which contains the list of positions where the value is found in the original list.

The List.Positions function is a powerful tool in Power Query that allows you to find all the occurrences of a specific value in a given list. The function is quite easy to use and the M code behind it is relatively simple.

By understanding how the List.Positions function works, you can simplify your data analysis and make it more efficient.

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)