List.MaxN

Updated on
Rick de Groot

List.MaxN is a Power Query M function that returns the maximum value(s) in a list based on specified count or condition and an optional comparisonCriteria. The function returns a list of maximum values meeting the specified criteria.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.MaxN(
   list as list,
   countOrCondition as any,
   optional comparisonCriteria as any,
   optional includeNulls as nullable logical,
) as list
ArgumentAttributeDescription
ListSpecifies the list from which the maximum value will be extracted.
countOrConditionOptionalSpecifies the number of values to return or a filtering condition. If a number is specified, a list of up to countOrCondition items in ascending order is returned. If a condition is specified, a list of items that initially meet the condition is returned. Once an item fails the condition, no further items are considered. If this parameter is null, the single smallest value in the list is returned.
Comparison CriteriaOptionalSets the rules for comparing list values, such as case-insensitive matching with Comparer.OrdinalIgnoreCase or culture-specific comparisons using Comparer.FromCulture. If not specified, it defaults to Comparer.Ordinal.
includeNullsOptionalAllows you to include or exclude null values in the comparison by setting it to true or false. If left unspecified, nulls are ignored.

Description

The List.MaxN function in the Power Query M language is designed to extract a specified number of the largest elements from a given list. This function is highly customizable, allowing you to set conditions for the values you wish to retrieve and specify how these values should be compared. The result is a new list containing the largest values based on the criteria you’ve set.

Examples

Let’s explore how we can use List.MaxN effectively with some practical examples.

Basic usage

Imagine you have a list of integers and you want to grab the top three largest numbers. The List.MaxN function simplifies this process for you:

// Output: { 40, 20, 10 }
List.MaxN( {10, 20, 5, 40, 1}, 3 )

In this example, the function organizes the list in descending order and returns the three largest integers: 40, 20, and 10.

What if your list contains a mix of data types, such as numbers and text? The List.MaxN function can still find the largest values based on the data type:

//  Output:{ "banana", "apple" }
List.MaxN( {10, "apple", 5, "banana"}, 2)

Here, the function discerns the two largest values, “banana” and “apple,” and returns them in a new list.

Dealing with Empty Lists

If you find yourself dealing with an empty list, List.MaxN will return an empty list as well:

//  Output: {}
List.MaxN( {},  5)

This built-in feature ensures that your code remains error-free and robust.

When the List Contains Fewer Items Than Specified

If your list contains fewer items than the number you’ve specified, the function will still operate smoothly:

//  Output: { 20, 10 }
List.MaxN( {10, 20 }, 5 )

Adding Custom Conditions

You can also introduce custom conditions to filter the values you want to extract. The function first sorts the list in descending order and then applies the condition:

//  Output: { 40, 20 }
List.MaxN( {10, 20, 5, 40, 1}, each _ >= 20 )

In another real-world example, you might want to extract all even numbers:

//  Output: { 900, 180, 20, 6 }
List.MaxN( 
  {  -500, -5, -400, 6, 20, 180, 900}, 
  each Number.IsEven(_)
)

This expression keeps returning values until it encounters the first value that is not even. In this case the values 900, 180, 20 and 6 are returned because the next highest value (-5) is not an even number. Remember, the function orders the input list before it applies the function.

Customizing Comparison Criteria

When it comes to comparing values, the List.MinN function offers flexibility through custom comparison criteria. By default, the function uses an ordinal comparison, which means it compares the Unicode characters of the underlying values to find the minimum.

// Output: { "c", "b", "a" }
List.MaxN(  { "a", "b", "c", "B" }, 3 )

This is equivalent to: 

List.MaxN( 
  { "a", "b", "c", "B" }, 
  3,  
  Comparer.Ordinal
)

For case-insensitive comparisons, you can switch to Comparer.OrdinalIgnoreCase:

// Output: { "c", "B", "b" }
List.MaxN( 
  { "a", "b", "c", "B" }, 
  3, 
  Comparer.OrdinalIgnoreCase
)

And for culture-sensitive comparisons, the function allows the use of Comparer.FromCulture.

// Output: { "æ", "c", "b" } 
List.MaxN( { "æ", "b", "c", "B" }, 3 )

By comparing using the Danish locale rules, the output changes. 

// Output: { "c", "B", "b" }
List.MaxN( 
  { "æ", "b", "c", "B" }, 
  3, 
  Comparer.FromCulture( "dk-DA")
)

Handling Null Values

By default, List.MaxN excludes null values in the comparison:

//  Output: { 10, 10, 5 }
List.MaxN( { 10, null, 5, 10, null }, 3 )

Which is equivalent to: 

List.MaxN( 
  { 10, null, 5, 10, null }, 
  3, 
  null, 
  false
  )

You can choose to include null values by setting the fourth argument to true. However, since null is the first value in Unicode, this feature is generally not useful for this function.

To conclude, the List.MaxN function is useful for extracting the largest values from a list. Whether you’re dealing with numbers, mixed data types, or even empty lists, this function does it all. Its ability to handle custom conditions and comparison criteria gives you lots of options for your particular scenario.

Learn more about List.MaxN in the following articles:

Other functions related to List.MaxN are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-maxn

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy