Table.Min - Table Function

Table.Min is a Power Query M function that identifies the smallest row in a table based on comparison criteria. The function returns the smallest row, or an optional default value if the table is empty.

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

Syntax

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

Description

The Table.Min function returns the row in a table that contains the smallest value based on a specified column or condition. It can be used in two main ways:

Column-based: You can provide a column name, and the function will return the row with the smallest value in that column.
Custom condition: Instead of a column, you can define a custom rule to determine what “minimum” means, like comparing negative values or using custom logic.

If the table is empty, you can provide an optional fallback value to return instead of an error.

Examples

For the first example, we are working with a table that contains books. Each book has a few details, including how many times it has been borrowed. We want to find the book that has been borrowed the least.

Here’s a simple table of books:

Table.First dataset as start in Power Query M

To find the book that was borrowed the fewest times, we use Table.Min and specify the column "TimesBorrowed". Here’s how it works:

Table.Min( Source, "TimesBorrowed" )

This tells Power Query to look at the TimesBorrowed column and find the smallest value, which is 20. The result from this function will be the following record:

Table.Min returns the minimum value of a column in Power Query M

If multiple rows have identical minimum values, only one of the rows will be returned as arecord.

Finding the Minimum Based on a Condition

Sometimes, you might need to define your own rules to find the “minimum” value. In the next example, instead of just looking for the smallest number in a specific column, we tell Power Query to find the row where the index shown negatively is the smallest.

Here’s the function:

Table.Min( Source, each -[Index] )

In this case, we aren’t looking at a specific column for the smallest value. We are telling Power Query to consider the Index column but to treat it as negative values (because of the each -[Index] part). This makes the function look for the largest index by treating negative numbers as if they were reversed. So, the row with the largest Index, which is 8, will be returned:

Table.Min returns the minimum of a condition in Power Query M

Handling Empty Tables: Providing a Fallback Value

What happens if your table is empty and you need a minimum value? Without any data, Table.Min wouldn’t normally return anything useful. That’s why there’s a way to provide a fallback value when the table is empty.

In this example, we create an empty table and ask for the minimum value in a column:

// Output: "FallbackValue"
let
  EmptyTable = #table( type table[ myColumn = text ], {} ),
  TableMinFallbackValue = Table.Min( EmptyTable, "myColumn", "FallbackValue" )
in
  TableMinFallbackValue

Since the table is empty, Power Query can’t find any value in the column myColumn. Instead of returning an error or nothing, the function returns the fallback value FallbackValue.

Full M Code

To try this yourself, please paste the following code into the advanced editor:

let
  Source = 
    #table(
      type table [ Index = Int64.Type, Title = text, Genre = text, TimesBorrowed = Int64.Type ],
      {
          { 1, "1984",                                  "Dystopian", 30 },
          { 2, "To Kill a Mockingbird",                 "Fiction",   50 },
          { 3, "The Great Gatsby",                      "Fiction",   45 },
          { 4, "Harry Potter and the Sorcerer's Stone", "Fantasy",   60 },
          { 5, "The Hobbit",                            "Fantasy",   35 },
          { 6, "Pride and Prejudice",                   "Romance",   40 },
          { 7, "The Catcher in the Rye",                "Fiction",   20 },
          { 8, "The Shining",                           "Horror",    25 }
      }
    ),
    TableMinColumn = Table.Min( Source, "TimesBorrowed" ),
    TableMinCondition = Table.Min( Source, each -[Index] ),
    EmptyTable = #table( type table[ myColumn = text], {} ),
    TableMinFallbackValue = Table.Min( EmptyTable, "myColumn", "FallbackValue" )
in
    TableMinFallbackValue

Other functions related to Table.Min are:

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