List.LastN

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.LastN

What is List.LastN?

List.LastN is a function that allows users to extract the last N items from a list in Power Query. The function takes two arguments: the list to extract from and the number of items to extract. For example, if we have a list of numbers {1, 2, 3, 4, 5} and we want to extract the last two items, we would use the function List.LastN({1, 2, 3, 4, 5}, 2), which would return the list {4, 5}.

How List.LastN Works

To understand how List.LastN works, we need to look at the M code behind the function. The M code for List.LastN is:

(list as list, count as number) as list =>

let

lastIndex = List.Count(list),

firstIndex = lastIndex – count + 1

in

List.Range(list, firstIndex, count)

Let's break down the code line by line:

(list as list, count as number) as list =>

This line defines the function and its arguments. The function takes two arguments: a list (which must be a list of values) and a count (which must be a number). The function returns a list.

let

This line starts a let statement, which allows us to define variables within the function. In this case, we are defining two variables: lastIndex and firstIndex.

lastIndex = List.Count(list),

This line sets the variable lastIndex to the length of the list.

firstIndex = lastIndex – count + 1

This line sets the variable firstIndex to the index of the first item in the list that we want to extract. To do this, we subtract the count argument (which tells us how many items to extract) from the lastIndex (which tells us the index of the last item in the list) and add 1.

in

This line marks the end of the let statement.

List.Range(list, firstIndex, count)

This line returns a new list that contains the items from the original list starting at the firstIndex and including count items. This is the result of the List.LastN function.

Using List.LastN in Power Query

List.LastN is a very useful function in Power Query, and it can be used in a variety of ways. Here are a few examples:

Extracting the Last N Rows from a Table

If we have a table in Power Query and we want to extract the last N rows, we can use List.LastN in combination with the Table.ToRows function. Here's an example:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUXKK1MvOzEwvAgA=”, BinaryEncoding.Base64)), Compression.Deflate)),

Last2Rows = List.LastN(Table.ToRows(Source), 2),

Output = Table.FromRows(Last2Rows, {“Column1”, “Column2”})

in

Output

In this example, we start with a table (which is created using the Table.FromRows function). We then use the List.LastN function to extract the last two rows of the table (using Table.ToRows to convert the table to a list first). Finally, we use Table.FromRows to convert the extracted rows back into a table.

Extracting the Last N Items from a CSV File

If we have a CSV file and we want to extract the last N rows, we can use List.LastN in combination with the Csv.Document function. Here's an example:

let

Source = Csv.Document(File.Contents(“C:data.csv”)),

Last50Rows = List.LastN(Source, 50),

Output = Table.FromList(Last50Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

Output

In this example, we start with a CSV file (which is loaded using the File.Contents function and then converted to a table using the Csv.Document function). We then use the List.LastN function to extract the last 50 rows of the table. Finally, we use Table.FromList to convert the extracted rows back into a table.

List.LastN is a powerful function in Power Query that allows users to extract the last N items from a list. Understanding the M code behind the function can help users to better utilize the function and integrate it into their Power Query workflows. By using List.LastN in combination with other Power Query functions, users can create powerful data transformations that can save time and improve data quality.

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)