A

B

C

D

E

F

G

H

I

J

L

#

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)

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)
   	Power BI Training for Facility Management Professionals (2 Days)
 	£1,990.00 Add to basketBuy Now
 
 
 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)