List.MinN

Updated on
Rick de Groot

List.MinN is a Power Query M function that returns the minimum value(s) in a list based on the specified count, condition, or comparison criteria. The function returns a list of up to countOrCondition items in ascending order or a list of items that initially meet the condition.

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

Syntax

List.MinN(
   list as list,
   countOrCondition as any,
   optional comparisonCriteria as any,
   optional includeNulls as nullable logical,
) as list
ArgumentAttributeDescription
ListSpecifies the list from which the minimum 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.MinN function retrieves a specified number of the smallest values from a given list. The function allows for customization, letting you set conditions for the values you want to extract and specify how the values should be compared. It returns a new list containing the smallest values based on the criteria you’ve set.

Examples

Let’s explore how we can use this function effectively with some practical examples.

Basic usage

Let’s say you have a list of integers and you’re interested in obtaining the three smallest values. The List.MinN function makes this task straightforward:

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

In this example, the function sorts the list in ascending order and returns the three smallest integers: 1, 5, and 10.

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

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

Here, the function identifies the two smallest numerical values, 5 and 10, and returns them in a new list.

Dealing with Empty Lists

If you’re working with an empty list, the List.MinN function will simply return another empty list:

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

This feature ensures that the function won’t throw an error, making your code more robust.

When the List Contains Fewer Items Than Specified

If your list has fewer items than the number you’ve specified to retrieve, the function will return all the available items:

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

Adding Custom Conditions

You can also provide a custom condition for the values to retrieve. Before testing the condition, the List.MinN function sorts all values in its list argument in ascending order. After doing that, only values that meet the condition are returned.

//  Output: { 1, 5, 10 }
List.MinN({10, 20, 5, 40, 1}, each _ < 20)

In another scenario you may want to retrieve all even numbers.

//  Output: { -500, -400 }
List.MinN( 
  {  -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 -500 and -400 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: { "B", "a", "b" }
List.MinN(  { "a", "b", "c", "B" }, 3 )

This is equivalent to: 

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

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

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

And for culture-sensitive comparisons, the function allows the use of Comparer.FromCulture. Without specifying a comparer, the following statement returns “B” on my machine:

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

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

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

Handling Null Values

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

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

Which is equivalent to: 

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

The fourth argument in the above expression determines whether to include null values. In case you want to exclude null, you can include them by setting the fourth argument to true:

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

The List.MinN function in the Power Query M language is useful for extracting the smallest 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.MinN in the following articles:

Other functions related to List.MinN are:

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

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