List.Max - List Function

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

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

Syntax

List.Max(
   list as list,
   optional default as any,
   optional comparisonCriteria as any,
   optional includeNulls as nullable logical,
) as any

Argument	Attribute	Description
List		Specifies the list from which the maximum value will be extracted.
Default	Optional	Specifies 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 Criteria	Optional	Sets 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.
includeNulls	Optional	Allows 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.Max function finds the largest 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.Max function with some examples.

Retrieving Maximum Value

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

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

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

// Output: "a"
List.Max( { "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 maximum value from such a diverse list:

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

Returning a default value

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

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

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.Max( { }, "My list is empty" )

Customizing Comparison Criteria

When it comes to comparing values, the List.Max 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 maximum.

// Output: "c"
List.Max(  { "a", "b", "c", "Z" } )

This is equivalent to:

List.Max( 
  { "a", "b", "c", "Z" }, 
  null, 
  Comparer.Ordinal
)

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

// Output: "Z"
List.Max( 
  { "a", "b", "c", "Z" }, 
  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 “æ” on my machine:

// Output: "æ"
List.Max( {"a", "af", "æ"} )

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

// Output: "af"
List.Max( 
  {"a", "af", "æ"}, 
  null,
   Comparer.FromCulture( "dk-DA")
)

Include Null Values

Another layer of customization you can add to the List.Max 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.

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

However, since null is the first value in the unicode reference, no characters will ever come before null. That makes this feature useless in this function.

To sum up, the List.Max function offers a high degree of flexibility, allowing you to tailor the comparison process to your specific 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.Max in the following articles:

Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.Max are:

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