List.LastN Function in Power Query M (5 Easy Examples)

List.LastN returns the last items of a list, either a set number of them or every item until one fails a condition scanning from the end. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to grab just the last few items from a list in Power Query, this is the function to reach for. In this article, I’ll show you how to use List.LastN with five practical examples.

Table of Contents

Syntax of List.LastN Function

List.LastN(list as list, optional countOrCondition as any) as any

where

list (required, list). The list to take items from.
countOrCondition (number or condition). Either a number or a condition. Give it a number and you get up to that many items from the end of the list. Give it a condition (an each function) and it keeps taking items, working backward from the end, until one fails the test. Although the signature marks it optional, leaving it out or passing null throws an error, so treat it as required.

Returns: a list with the trailing items of list. With a number, you get up to that many items. With a condition, you get every item from the end before the first one that fails it.

In plain terms, it takes items from the back of a list, and you decide where it stops, either by count or by condition.

Example 1: Get the last N items with a count

Say you have a list of five numbers and you only want the last two.

let
    Source = List.LastN({1,2,3,4,5},2)
in
    Source

Result: {4, 5}

The count 2 takes the last two items in list order. Everything before them is ignored, and the result keeps the original order, so you get {4, 5}, not {5, 4}.

Example 2: Use a condition to take items until one fails

Here you want the trailing run of values greater than 10, working backward from the end of the list.

let
    Source = List.LastN({4,8,15,3,20,25},each _>10)
in
    Source

Result: {20, 25}

Scanning from the end, 25 and 20 both pass each _>10. The next item back is 3, which fails, so the function stops there.

The earlier 15 would pass the test, but it sits before the 3 that stopped the scan, so it never gets returned. This is the part that trips people up. List.LastN is not a filter, it only takes items while the condition holds from the end.

If you want every item that passes, no matter where it sits in the list, use List.Select instead. It would also keep the earlier 15.

Example 3: Ask for more items than the list has

Here we ask for 5 items from a list of only 3 numbers.

let
    Source = List.LastN({10,20,30},5)
in
    Source

Result: {10, 20, 30}

No error. The count means “up to that many”, so when the list is shorter than the count, you just get the whole list back.

Example 4: Get the last 3 values from a table column

Say you have a SalesTbl table with monthly amounts and you want the last 3 months.

Here is the starting data:

Month	Amount
Jan	1200
Feb	1500
Mar	1800
Apr	1100
May	2000
Jun	2400

Pull the column out as a list, then take the last 3 values:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesTbl"]}[Content],
    LastThree = List.LastN(Source[Amount],3)
in
    LastThree

Result: {1100, 2000, 2400}

Source[Amount] turns the column into a list, and List.LastN takes the last 3 values in their original order, so you get the Apr, May, and Jun amounts.

Example 5: Total the last 3 values from a table column

Building on the same table, wrap the list in List.Sum to get the trailing-3 total.

Here is the starting data:

Month	Amount
Jan	1200
Feb	1500
Mar	1800
Apr	1100
May	2000
Jun	2400

let
    Source = Excel.CurrentWorkbook(){[Name="SalesTbl2"]}[Content],
    LastThreeTotal = List.Sum(List.LastN(Source[Amount],3))
in
    LastThreeTotal

Result: 5500

List.LastN(Source[Amount],3) returns the last 3 amounts, {1100, 2000, 2400}, and List.Sum adds them up. Swap in List.Average for a trailing average instead.

Things to keep in mind with List.LastN

The signature says as any, but you always get a list back. Even asking for 1 item returns a one-item list, not the bare value. Use List.Last when you want the item itself.
The condition form short-circuits at the first failure from the end. It stops scanning the moment an item fails, so any matching items that sit before that point are never returned (Example 2). For every match regardless of position, use List.Select.
A count larger than the list length returns the whole list. There is no error, so it is safe for “give me up to N” patterns (Example 3).
List.FirstN is the mirror function. It takes items from the start of the list instead, with the same count or condition options.

Common questions about List.LastN

What is the difference between List.LastN and List.FirstN?

List.FirstN takes items from the start of the list, while List.LastN takes them from the end. The argument rules are identical, including the count-or-condition behavior and the “up to N” handling when the count is too big.

How do I get the last row of a table?

For whole rows, use Table.LastN, which keeps the trailing rows of a table. List.LastN works on a single list, so for the last few values of one column, pull the column out with Source[Column] and wrap it like Example 4 does.

List of All Power Query Functions

Related Power Query Functions / Articles: