Table.LastN

In this article, we will take a closer look at the M code behind the Table.LastN function and how it can be used to improve your data analysis.

What is the Table.LastN Function?

The Table.LastN function is a Power Query function that allows you to extract the last n rows of a table. It takes two arguments: the table to extract the rows from, and the number of rows to extract.

Here is a basic example of how to use the Table.LastN function:

In this example, we are extracting the last 10 rows from the table named “Table1”. The resulting table will contain only the last 10 rows of the original table.

How Does the Table.LastN Function Work?

The Table.LastN function works by first determining the total number of rows in the input table. It then subtracts the number of rows to extract from this total to determine the index of the first row to keep. Finally, it filters out all rows before this index.

Here is the M code for the Table.LastN function:

As you can see, the function takes two arguments: a table and either a number or a condition. If a number is passed in, it is used as the number of rows to extract. If a condition is passed in, it is used to filter the table and count the number of rows that match the condition.

Advanced Usage of Table.LastN

The Table.LastN function can be used in a variety of ways to extract and manipulate data. Here are a few examples:

Extract the Last N Rows by Group

You can use the Table.Group function to group your data by a specific column and then extract the last n rows from each group. Here is an example:

In this example, we are grouping the data by the “Category” column and then extracting the last 2 rows from each group.

Extract the Last N Rows by Date

You can use the Date.AddMonths function to calculate a date n months ago, and then filter your data to only include rows that are more recent than this date. Here is an example:

In this example, we are filtering the data to only include rows that are more recent than 3 months ago, and then extracting the last 10 rows from this filtered table.

Extract the Last N Rows with a Condition

You can use a custom function to filter your data and extract the last n rows that match a specific condition. Here is an example:

In this example, we are filtering the data to only include rows where the “Sales” column is greater than 1000, and then extracting the last 10 rows from this filtered table.

The Table.LastN function is a powerful tool in Power Query that allows you to extract a specified number of rows from the end of a table. By understanding the M code behind this function, you can use it in a variety of ways to extract and manipulate your data.

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

Upcoming Courses

Contact Us