Table.Min
Returns the row that has the lowest value in a column.
Table.Min(table as table, column as text, optional default as any) as anyReturns: The row as a record, or the default value if the table is empty.
What it does
Table.Min finds the single row with the lowest value in one column and returns the whole row as a record. It is the mirror of Table.Max. Use it for the earliest date in a file, the oldest open invoice or the smallest balance.
Syntax and arguments
- table: the table to search. Required.
- column: the name of the column to compare, in quotes. Required.
- default: the value to return if the table is empty. Optional, but advisable when the table might be empty.
Example
An aging review needs the due date of the oldest sample order, which is the start of the period under review.
let
Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
Earliest = Table.Min(Orders, "orderDate", null),
EarliestDate = Earliest[orderDate]
in
EarliestDate
The row with the earliest order date is found, and the date is read from it. The result is the start date of the period in the data.
Example: a second, different use
The oldest open invoice in a receivables list is found, so the reviewer can start with it.
let
Source = Excel.CurrentWorkbook(){[Name = "OpenInvoices"]}[Content],
Typed = Table.TransformColumnTypes(Source, {{"DueDate", type date}}),
Oldest = Table.Min(Typed, "DueDate", null)
in
Oldest
Things to watch
- The result is a record. Read the field you need from it.
- Provide a default record if the table might be empty, or the next step fails.
- Ties return one of the tied rows, and which one depends on the order of the table.
- Type dates and numbers before you use the function. Text is compared as text.
- Query folding: check View Native Query, since the row lookup often does not fold.
In business terms
The earliest invoice date and the smallest open balance are the usual starting points for an aging review or a cut-off test. Finding them in the query makes the starting point visible to the reviewer.