List.Max Function in Power Query M (5 Easy Examples)

If you want to pull the single largest value out of a list in Power Query, whether that’s the highest number, the longest piece of text, or the most recent date, the List.Max function is what you reach for.

In this article I’ll walk you through how it works with a handful of plain examples.

Table of Contents

Syntax of List.Max Function

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

where

list (required, list). The list of values you want the maximum of.
default (optional, any). The value to return when the list is empty. Leave it out and an empty list returns null.
comparisonCriteria (optional, any). A function that transforms each value before it gets compared. Pass null to compare the values as they are.
includeNulls (optional, nullable logical). Whether null values in the list count when working out the maximum. The default is true.

Returns: a single value of whatever type the list holds (the maximum item). If the list is empty and no default is given, it returns null.

In plain terms, you hand it a list and it gives you back the biggest item in that list.

Example 1: Get the highest number in a list

Find the largest value in a short list of numbers.

let
    Source = List.Max({18,42,7,29,33})
in
    Source

Result: 42

42 is the biggest number in the list, so that’s what comes back.

Example 2: Get the maximum from a table column

The most common real use is pulling the max out of one column of a table.

Say you have a SalesData table with a Rep and a Units column, and you want the highest Units figure.

Here is the starting data:

Rep	Units
Alice	240
Ben	310
Carla	185
Devon	295

Grab the Units column as a list, then run List.Max over it:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    Units = Source[Units],
    Result = List.Max(Units)
in
    Result

Source[Units] pulls the column out as a list, and List.Max returns the top number from it.

Result: 310

Ben’s 310 is the highest, so that’s the value you get.

Example 3: Return a fallback when the list is empty

An empty list has no maximum, so on its own List.Max hands back null. The default argument lets you return something useful instead.

let
    Source = List.Max({},-1)
in
    Source

Result: -1

The list is empty, so instead of null you get the -1 you passed as the default.

Example 4: Find the longest word with comparisonCriteria

You don’t have to compare the values themselves. With comparisonCriteria you can compare something derived from each value, like its length.

Here you want the longest word, not the one that sorts last alphabetically.

let
    Words = {"apple","fig","blueberry","kiwi"},
    Result = List.Max(Words,null,each Text.Length(_))
in
    Result

Result: blueberry

Text.Length(_) measures each word, so the comparison runs on character counts. blueberry is the longest at nine letters, so it wins.

Example 5: Ignore nulls in the list

When a list mixes real values with null, you can tell List.Max to skip the nulls by setting includeNulls to false.

let
    Source = List.Max({12,null,8,25,null},null,null,false)
in
    Source

Result: 25

The null entries are dropped from the comparison, and 25 is the largest of the values that remain.

Things to keep in mind with List.Max

It compares by the list’s natural order. Numbers compare by size, text compares alphabetically ("zebra" beats "apple"), and dates compare chronologically (latest wins).
comparisonCriteria transforms before it compares, it does not change the result. Example 4 compares by Text.Length, but you still get the original word back, not its length.
Mixing types throws an error. A list like {1,"a"} gives Expression.Error: We cannot apply operator > to types Number and Text. Clean the list to one type first.
includeNulls only matters with the fourth argument. To reach it you have to supply default and comparisonCriteria (use null for either if you don’t need them), as in Example 5.
Guard an empty result with a default or ??. With no default, an empty list returns null. Pass a default, or wrap the call with the coalesce operator, as in List.Max(myList) ?? 0.

Common questions about List.Max

How do I get the whole row with the maximum value, not just the value?

List.Max only returns the single largest value. To get the full record, sort the table descending on that column with Table.Sort and take the first row, or filter with Table.SelectRows where the column equals List.Max of the column.

Is there a List.Maximum function?

No. The function is List.Max. There’s a matching List.Min for the smallest item, and List.MaxN when you want the top N values rather than just one.

List of All Power Query Functions

Other Power Query Related Articles:

Related Power Query Functions / Articles: