List.Max
Returns the largest value in a list.
List.Max(list, optional default)Returns: The largest item, or the default value when the list is empty.
What it does
List.Max finds the largest item in a list. For numbers it is the highest figure, for dates it is the latest date, and for text it is the last value in alphabetical order. Use it to find the largest single expense, the most recent payment date or the peak balance in a period.
Syntax and arguments
list: the items to search. Nulls are ignored when the list has other values.default(optional): the value returned when the list is empty. Without it, an empty list gives null.
Example
A controller checks whether any single expense in a month went over the approval limit, and wants the largest expense for the review pack.
let
Expenses = {420, 1890, 75, 3150, 260},
LargestExpense = List.Max(Expenses, 0),
OverLimit = LargestExpense > 2500
in
OverLimit
LargestExpense is 3150, so OverLimit is true. The default 0 keeps the test working even if the list is empty.
Example: a second, different use
Finding the most recent payment date for a customer: List.Max over a list of payment dates gives the date of the latest payment.
Things to watch
- An empty list returns null unless you give a default. Test for null before you compare, or provide a default as shown.
- The maximum is one value. It does not say which row it came from. For the whole row, sort or use Table.Max.
- Text is compared alphabetically. A list of numbers stored as text can give a surprising maximum, so convert the type first.
- A maximum is sensitive to a single bad value, such as a typing error that adds an extra zero. Look at the source row before you report it.
In business terms
Largest expense, peak utilization of a credit line, most recent transaction and the highest balance in an aging bucket are typical uses. A maximum is a good first check on a column for data that is out of range.