Table.MinN

Updated on

Table.MinN is a Power Query M function that obtains the smallest row(s) in a table based on comparison criteria and a count or condition. The function returns a list of rows that meet the specified criteria.

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

Syntax

Table.MinN(
   table as table,
   comparisonCriteria as any,
   countOrCondition as any,
) as table

Description

The Table.MinN function returns the smallest rows from a table based on a specified column or condition. It sorts the rows by the given criteria, then filters the result based on the third argument. Note the sorting algorithm cannot guarantee a fixed sorted result.

You can use one of the following approaches for the countOrCondition parameter:

  • If a number is provided, the function returns up to that many items in ascending order.
  • If a condition is provided, the function returns items that meet the condition, stopping as soon as one does not match.

Examples

Let’s go through a couple of examples to see how Table.MinN works.

In this example, we have a table of books, each with details like how many times it has been borrowed. We want to find the three books that have been borrowed the least.

Here’s the table of books:

Table.First dataset as start in Power Query M

Finding the Three Smallest Values in a Column

To find the three books with the smallest TimesBorrowed values, we use this function:

Table.MinN( Source, "TimesBorrowed", 3 )

This tells Power Query to look at the TimesBorrowed column and return the three rows with the smallest values. The result will be:

Table.MinN returns minimum rows based on count in Power Query M

These three books have been borrowed the least number of times. Also notice how the rows are sorted by the TimesBorrowed column. The operation does both filtering and sorting at the same time.

It’s important to remember that if 5 rows would represent the lowest three values, the function still only returns three rows and does not guarantee a fixed sorted result.

Using a Custom Condition to Find Rows

You can also apply a condition to decide what counts as a “small value.” In this example, instead of limiting the result to a specific number of rows, we want to find all the books that have been borrowed fewer than 40 times.

Here’s how we can do it:

Table.MinN( Source, "TimesBorrowed", each [TimesBorrowed] < 40 )

In this case, we’re asking Power Query to return all rows where TimesBorrowed is less than 40. The function will return:

Table.MinN returns minimum rows based on conditions in Power Query M

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 }
      }
    ),
    TableMinN_WithCount = Table.MinN( Source, "TimesBorrowed", 3 ),
    TableMinN_WithCondition = Table.MinN( Source, "TimesBorrowed", each [TimesBorrowed] < 40 )
in
    TableMinN_WithCondition

Other functions related to Table.MinN are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy