List.MaxN

D

Back to top

T

Back to top

#

Back to top

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

What is List.MaxN?

List.MaxN is a Power Query M function that takes two arguments: a list and a number n. It returns the maximum n values from the list. For example, if we have a list {1, 5, 2, 4, 3} and we want to get the top three values, we can use List.MaxN(list, 3) which will return {5, 4, 3}.

The M code behind List.MaxN

The M code behind List.MaxN is quite simple. It first sorts the list in descending order and then takes the first n values from the sorted list. Here is the M code:

(list as list, n as number) =>

let

sortedList = List.Sort(list, (a, b) => b – a),

result = List.FirstN(sortedList, n)

in

result

Let's break down this code step by step:

1. It takes two arguments: list and n.

(list as list, n as number) =>

2. It sorts the list in descending order using List.Sort function and a lambda function. In this lambda function, we subtract b from a to sort the list in descending order.

sortedList = List.Sort(list, (a, b) => b – a)

3. It takes the first n values from the sorted list using List.FirstN function.

result = List.FirstN(sortedList, n)

4. It returns the result.

result

Understanding the M code

The M code behind List.MaxN is quite simple and easy to understand. Let's take a closer look at each step.

Sorting the list

The first step is to sort the list in descending order using List.Sort function. The List.Sort function takes two arguments: the list to be sorted and a lambda function that compares two values. In our case, we want to sort the list in descending order, so we subtract b from a. This lambda function returns a negative value if a is greater than b, which means the two values need to be swapped.

Taking the top values

Once the list is sorted, we can easily take the top n values using List.FirstN function. This function takes two arguments: the list to be truncated and the number of values to be taken from the list. In our case, we want to take the top n values, so we pass n as the second argument to List.FirstN function.

Returning the result

Finally, we return the result. The result is a list of n values which are the top n values from the original list.

In this article, we explored the M code behind List.MaxN function in Power Query. We learned that List.MaxN function sorts the list in descending order and takes the top n values from the sorted list. We also understood how to modify the lambda function to sort the list in ascending order. Understanding the M code behind Power Query functions can help us write more efficient and effective queries.

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)