Table.MinN

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

Returns the smallest row(s) in the table, given the comparisonCriteria. After the rows are sorted, the countOrCondition parameter must be specified to further filter the result. Note the sorting algorithm cannot guarantee a fixed sorted result. The countOrCondition parameter can take multiple forms:

  • If a number is specified, a list of up to countOrCondition items in ascending order is returned.
  • If a condition is specified, a list of items that initially meet the condition is returned. Once an item fails the condition, no further items are considered.

Examples

Find the row with the smallest value in column [a] with the condition [a] < 3, in the table. The rows are sorted before the filter is applied.

Table.MinN( 
    Table.FromRecords( {
        [a = 2, b = 4],
        [a = 0, b = 0],
        [a = 6, b = 4]
    } ),
    "a",
    each [a] < 3
 )

 /* Output: 
Table.FromRecords( {
    [a = 0, b = 0],
    [a = 2, b = 4]
} )
 */

Find the row with the smallest value in column [a] with the condition [b] < 0, in the table. The rows are sorted before the filter is applied.

// Output: Table.FromRecords( {} )
Table.MinN( 
    Table.FromRecords( {
        [a = 2, b = 4],
        [a = 8, b = 0],
        [a = 6, b = 2]
    } ),
    "a",
    each [b] < 0
 )

Other functions related to Table.MinN are:

BI Gorilla Blog

Last update: August 28, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-minn
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.