Table.Min Function in Power Query M (5 Easy Examples)

If you want to find the row with the lowest value in a Power Query table, like the item with the fewest units in stock, the Table.Min function is what you reach for. You give it a table and a way to compare rows, and it hands back the single bottom row. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Table.Min Function

Table.Min(table as table, comparisonCriteria as any, optional default as any) as any

where

table (required, table). The table to scan for the smallest row.
comparisonCriteria (required, any). How rows are ranked. Pass a column name as text ("Units"), a list of column names to sort by several keys, or an each expression that computes the value to rank by (each [Units]*[Weight]).
default (optional, any). The value returned when the table is empty. Without it, an empty table throws an error.

Returns: the smallest row as a record (the whole winning row, not a single number). To get one value out of it, access a field like [Item]. If the table is empty, it returns default.

In plain terms, you give it a table and tell it how to rank the rows, and it returns the bottom row.

Example 1: Find the bottom row by a column

Say you have a WarehouseStock table of network-hardware inventory:

Item	Units	Weight
Cables	340	0.2
Adapters	75	0.15
Routers	210	1.1
Switches	130	1.8

You want the item with the fewest units in stock. Type the number columns first, rank by "Units", then pull the Item field off the winning row:

let
    Source = Excel.CurrentWorkbook(){[Name="WarehouseStock"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type},{"Weight",type number}}),
    BottomRow = Table.Min(Typed,"Units"),
    Result = BottomRow[Item]
in
    Result

Result: Adapters

Adapters has the fewest units (75), so Table.Min returns its row, and [Item] reads the name off it.

Example 2: See the whole winning row

Table.Min returns a record, the entire bottom row, not just one value. To see all of it, wrap the result in Record.ToTable, which lays the record out as a Name/Value table.

Using the same WarehouseStock shape:

Item	Units	Weight
Cables	340	0.2
Adapters	75	0.15
Routers	210	1.1
Switches	130	1.8

let
    Source = Excel.CurrentWorkbook(){[Name="WarehouseStock2"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type},{"Weight",type number}}),
    BottomRow = Table.Min(Typed,"Units"),
    Result = Record.ToTable(BottomRow)
in
    Result

The result shows every field of the Adapters row:

Name	Value
Item	Adapters
Units	75
Weight	0.15

Because Table.Min gives you the row, you can read any column off it, not just the one you ranked by.

Example 3: Rank by a computed value

comparisonCriteria does not have to be a plain column. Pass an each expression to rank by something you calculate per row, like total shipping weight ([Units]*[Weight]).

Using the same WarehouseStock data, rank by total weight and read the Item:

let
    Source = Excel.CurrentWorkbook(){[Name="WarehouseStock3"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type},{"Weight",type number}}),
    BottomRow = Table.Min(Typed,each [Units]*[Weight]),
    Result = BottomRow[Item]
in
    Result

Result: Adapters

Adapters has the lowest total weight (75*0.15=11.25), so it wins even though Cables holds far more units. Ranking by a computed value can pick a different winner than ranking by a single column. If you need the full ranked order rather than just the bottom row, sort the table instead.

Example 4: Break a tie with a list of columns

When two rows tie on the first criterion, pass a list of column names so Table.Min falls through to the next column. It ranks by the first, then the second, and so on.

Here the Units column has a real tie (Adapters and Switches both hold 75):

Item	Units	Weight
Cables	340	0.2
Adapters	75	0.4
Routers	210	1.1
Switches	75	0.15

Rank by Units first, then Weight to break the tie:

let
    Source = Excel.CurrentWorkbook(){[Name="WarehouseStock4"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type},{"Weight",type number}}),
    BottomRow = Table.Min(Typed,{"Units","Weight"}),
    Result = BottomRow[Item]
in
    Result

Result: Switches

Both items have 75 units, so Table.Min checks Weight next. Switches (0.15) is lighter than Adapters (0.4), so it wins. A list of columns turns an arbitrary tie into a deterministic result.

Example 5: Handle an empty table with a default

On an empty table, Table.Min throws an error unless you pass the third argument. The default value is what comes back when there are no rows to rank.

let
    Empty = #table(type table [Item=text,Units=number],{}),
    Result = Table.Min(Empty,"Units",-1)
in
    Result

Result: -1

There are no rows, so Table.Min returns the default of -1 instead of failing. Use a fallback that makes sense downstream, like -1, null, or a placeholder record.

Things to keep in mind with Table.Min

Untyped number columns compare as text. Columns from Excel.CurrentWorkbook arrive as type any, and Table.Min then ranks them lexically, so "130" can come before "75". Set the column to a number type first with Table.TransformColumnTypes (as every example above does).
It returns the row, not the value. You get a record back, so reading the result directly gives you the whole row. Field-access it ([Item]) or convert it with Record.ToTable to get something usable (Examples 1 and 2).
An empty table errors without a default. Calling Table.Min(EmptyTable,"Units") on a table with no rows throws an Expression.Error. Pass the third argument to get a safe fallback instead (Example 5).
Single-column ties are arbitrary, list ties are not. Ranking by one column and hitting a tie returns one unspecified row. Pass a list of columns (Example 4) when you need the winner to be deterministic.
To work with the matching rows, not just one, filter instead. Table.Min only ever returns a single record. To keep every row that meets a condition, use Table.SelectRows on a value you derive from the minimum.

Common questions about Table.Min

What is the difference between Table.Min and List.Min?

List.Min takes a list of values and returns the single smallest value, like List.Min({3,9,5}) giving 3. That is the approach to use when you just want the lowest number in one column.

Table.Min takes a table plus a ranking criterion and returns the whole winning row as a record. Reach for List.Min when you already have one column of values, and Table.Min when you need the rest of the row that owns the minimum.

List of All Power Query Functions

Related Power Query Functions / Articles: