List.Min

Updated on

List.Min is a Power Query M function that finds the minimum item in a list, or returns an optional default value if the list is empty. The function returns the minimum item with an optional comparisonCriteria for custom comparisons.

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

Syntax

List.Min(
   list as list,
   optional default as any,
   optional comparisonCriteria as any,
   optional includeNulls as nullable logical,
) as any
ArgumentAttributeDescription
ListSpecifies the list from which the minimum value will be extracted.
DefaultOptionalSpecifies a default return value for when the list is empty. If this argument is not provided, the function will return null for an empty list.
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.Min function finds the smallest value in a given list, which can include various types of data like numbers or text. You can customize how it compares values and what it returns if the list is empty. It also allows you to include or exclude null values in the comparison.

Examples

Let’s explore the List.Min function with some examples.

Retrieving Minimum Value

Imagine you have a list that contains monthly sales figures, and you’re interested in identifying the month with the lowest sales. You can use the following expression to find the smallest sales figure in the list:

//  Output: 500
List.Min( { 500, 550, 520, 530, 540 } )

While you might naturally associate the retrieval of minimum values with numerical data, this function is versatile enough to work with text as well. For example:

// Output: "B"
List.Min( { "a", "B"} )

Your list may contain a variety of data types, ranging from numbers and text to logical values like true or false. Here’s how you can extract the minimum value from such a diverse list:

// Output: 1
List.Min( { 1, "ABC", true } )

Returning a default value

What happens if your list is empty? By default, the List.Min function will return a null value.

// Output: null
List.Min( { } )

However, you have the option to specify a default value that will be returned if the list is empty. This can be particularly useful for providing more informative output.

// Output: "My list is empty"
List.Min( { }, "My list is empty" )

Customizing Comparison Criteria

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

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

This is equivalent to:

List.Min( 
  { "a", "b", "c", "B" }, 
  null, 
  Comparer.Ordinal
)

If you prefer a case-insensitive comparison, you can switch the comparer function to Comparer.OrdinalIgnoreCase.

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

For those who require 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"
List.Min( { "æ", "b", "c", "B" } )

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

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

Include Null Values

Another layer of customization you can add to the List.Min function is the treatment of null values during the comparison process. You have the option to either include or exclude null values, depending on your needs.

If you want null values to be considered in the comparison, you can specify this by adding a boolean value of true as the fourth argument in the function. Here’s an example:

// Output: null
= List.Min( 
  { null, -1, -2 }, 
  null, 
  null,
  true
)

In this case, null is considered smaller than -1 or -2.

On the other hand, if you prefer to ignore null values during the comparison, you can set the fourth argument to false. Here’s how you can do it:

// Output: -2
= List.Min( 
  { null, -1, -2 }, 
  null, 
  null,
  false
)

In this example, the output returns -2 since it does not include null values. That shows that the choice to include or exclude null values can have significant implications depending on the data set and what you are trying to achieve.

To sum up, the List.Min function offers a high degree of flexibility, allowing you to tailor the comparison process to your requirements. Whether you’re dealing with mixed data types, empty lists, or null values, this function has options to accommodate your needs.

Learn more about List.Min in the following articles:

Other functions related to List.Min are:

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