Table.MinN

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.MinN

Understanding the Table.MinN function

Before delving into the M code behind the Table.MinN function, it is important to understand how this function works. The syntax of the function is as follows:

Table.MinN(Table as table, count as number, optional comparisonCriteria as any) as table

The first argument is the table from which the N smallest values will be returned. The second argument is the count of the smallest values to return. The optional third argument is the comparison criteria used to determine the smallest values. If this argument is not provided, the function will use the default comparison criteria.

The M code behind the Table.MinN function

The M code behind the Table.MinN function is not as complicated as one might think. The code is designed to sort the table in ascending order based on the comparison criteria and then return the top N values. Here is the M code for the Table.MinN function:

(Table as table, count as number, optional comparisonCriteria as any) as table =>

let

sortedTable = Table.Sort(Table, comparisonCriteria, Order.Ascending),

minNRows = Table.FirstN(sortedTable, count)

in

minNRows

As you can see, the code is quite straightforward. The table is first sorted in ascending order based on the comparison criteria. The first N rows of the sorted table are then returned as the result.

Examples of using the Table.MinN function

To better understand the M code behind the Table.MinN function, let’s look at some examples of how this function can be used.

Example 1: Using the default comparison criteria

Suppose we have a table of sales data for a retail store. We want to find the 3 products with the lowest sales. Here is the M code to achieve this:

Table.MinN(SalesData, 3)

In this example, the default comparison criteria is used, which is the first column of the table in ascending order.

Example 2: Using a custom comparison criteria

Suppose we have a table of employee data that includes their salaries. We want to find the 5 employees with the lowest salaries. Here is the M code to achieve this:

Table.MinN(EmployeeData, 5, each [Salary])

In this example, a custom comparison criteria is used, which is the Salary column of the table in ascending order.

The Table.MinN function is a powerful function in Power Query that is used to return a table with the N smallest values. The M code behind this function is quite simple and straightforward. By understanding the M code, you can better utilize this function to clean and transform your data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)