Table.RemoveLastN

D

Back to top

T

Back to top

#

Back to top

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

What is the Table.RemoveLastN Function?

The Table.RemoveLastN function is a Power Query function that allows users to remove a specified number of rows from the end of a table. This function is useful when working with large tables and you only need to use a portion of the data. By removing unnecessary rows at the end of the table, you can reduce the size of the table and improve performance.

Understanding the M Code Behind Table.RemoveLastN

The M code behind the Table.RemoveLastN function is fairly simple. This function takes two arguments: the table you want to modify and the number of rows you want to remove from the end of the table. Here is the M code for the Table.RemoveLastN function:

(Table as table, CountOrCondition as any) as table =>

let

Count = if Value.Is(Value.Type(CountOrCondition), type number) then CountOrCondition else Table.RowCount(Table.Buffer(Table.SelectRows(Table, CountOrCondition))),

Result = if Count >= Table.RowCount(Table) then #table({}, {}) else Table.FirstN(Table, Table.RowCount(Table) - Count)

in

Result

Let’s break down this code to understand how it works.

Arguments

The first line of the M code defines the arguments for the Table.RemoveLastN function. The function takes two arguments:

– Table: The table you want to modify

– CountOrCondition: The number of rows you want to remove from the end of the table, or a function that returns a logical value for each row in the table

Calculating the Count

The next few lines of code calculate the number of rows to remove from the end of the table. The CountOrCondition argument can either be a number or a function that returns a logical value for each row in the table. If it is a number, then that number is used as the count. If it is a function, then the number of rows to remove is calculated by counting the number of rows for which the function returns true.

Removing Rows from the End of the Table

The last few lines of code remove the specified number of rows from the end of the table. If the count is greater than or equal to the number of rows in the table, then an empty table is returned. Otherwise, the first N rows of the table are returned, where N is the number of rows in the table minus the count.

The Table.RemoveLastN function is a powerful tool that allows users to remove a specified number of rows from the end of a table in Power Query. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how to use it effectively in your data cleaning and transformation tasks.

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)