# List.MaxN

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``````

## 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 )``````

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.