Table.MaxN

D

Back to top

T

Back to top

#

Back to top

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

Introduction to Table.MaxN

The Table.MaxN function is used to return the top N rows from a table based on a specific column. This function is particularly useful when working with large datasets where you need to filter out the top N rows based on a specific criteria. The syntax for the Table.MaxN function is as follows:

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

The first argument of this function is the table that you want to filter. The second argument is the number of rows that you want to return. The third argument is optional and is used to specify the criteria that you want to use for filtering the rows.

Understanding the M Code Behind Table.MaxN

When you use the Table.MaxN function in Power Query, the M code behind it is automatically generated. However, it is useful to understand the M code behind the function in order to customize it for your specific needs. The M code for the Table.MaxN function is as follows:

let

Source = table,

Column = Table.Column(Source, column),

SortList = List.Sort(Column, comparisonCriteria, Order.Descending),

TakeNRows = List.FirstN(SortList, count),

FilterTable = Table.SelectRows(Source, each List.Contains(TakeNRows, Table.Column(Source, column)))

in

FilterTable

Let’s break down this code into its individual components.

Step 1: Define the Source Table

The first step in the M code is to define the source table that you want to filter. This is done using the following code:

Source = table,

The “table” argument that is passed to the Table.MaxN function is assigned to the variable “Source”.

Step 2: Extract the Column to be Filtered

The next step is to extract the column that you want to filter. This is done using the following code:

Column = Table.Column(Source, column),

The “column” argument that is passed to the Table.MaxN function is used to extract the column from the “Source” table.

Step 3: Sort the Column

The next step is to sort the extracted column in descending order. This is done using the following code:

SortList = List.Sort(Column, comparisonCriteria, Order.Descending),

The “List.Sort” function is used to sort the “Column” list in descending order based on the “comparisonCriteria” argument. The sorted list is assigned to the variable “SortList”.

Step 4: Take the Top N Rows

The next step is to take the top N rows from the sorted list. This is done using the following code:

TakeNRows = List.FirstN(SortList, count),

The “List.FirstN” function is used to take the top “count” rows from the “SortList” list. The resulting list is assigned to the variable “TakeNRows”.

Step 5: Filter the Source Table

The final step is to filter the source table based on the top N rows that were extracted in step 4. This is done using the following code:

FilterTable = Table.SelectRows(Source, each List.Contains(TakeNRows, Table.Column(Source, column)))

The “Table.SelectRows” function is used to filter the “Source” table based on the condition that the column values must be contained within the “TakeNRows” list.

The Table.MaxN function is a powerful tool that can be used to filter out the top N rows from a table based on a specific criteria. Understanding the M code behind this function can help you customize it for your specific needs. By breaking down the M code, we can see how the function works and how it can be modified to suit different use cases.

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)