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:
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:
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:
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
Related functions
Other functions related to Table.MinN are:
- Table.AggregateTableColumn
- Table.First
- Table.FirstN
- Table.FirstValue
- Table.Last
- Table.LastN
- Table.Max
- Table.MaxN
- Table.Min
- Table.SingleRow
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy