List.LastN

Updated on
Rick de Groot

List.LastN is a Power Query M function that returns the last item or items of a list based on a specified number or condition. The function returns a list with the specified number of items or items meeting the condition, starting at the end of the list.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The List.LastN function returns the final elements in a list. Should the list be empty, it triggers an exception and returns null. An optional argument, named countOrCondition, can be used for collecting multiple elements or applying a filter. The countOrCondition argument can be defined in two manners:

  • When a number is given, that many elements from the end are returned.
  • If a condition is given, it starts from the list’s end and returns all elements that initially satisfy the condition. Once an element fails to meet the condition, the function stops considering additional items.

Examples

The List.LastN function allows users to extract the last ‘N’ items from a list, either based on a specified count or a condition. Let’s explore its applications with some examples.

Retrieving LastN Values

Suppose you have a list of monthly sales and want to analyze the last quarter. The following expression returns the sales of the last three months:

//  Output: { 520, 530, 540 }
List.LastN( { 500, 550, 520, 530, 540 }, 3 )

Your list might comprise a mix of data types, from numbers to text and logical values. Here’s how you can extract the last two items from such a list:

//  Output:{ "ABC", true }
List.LastN( { 1, "ABC", true }, 2)

Applying Custom Conditions

One of the standout features of List.LastN is its ability to work with custom conditions. By supplying a function as a condition, the function will extract values from the end of the list until it encounters a value that doesn’t meet the specified criteria.

For instance, to fetch the initial positive numbers starting at the end of list, you can use:

// Output: {7, 8, 2}
List.LastN( {3, 4, 5, -1, 7, 8, 2}, each _ > 0 )

If you’re interested in extracting only the odd numbers from a list, the Number.IsOdd function comes in handy: 

// Output: { 7, 9 }
List.LastN( { 3, 5, 4, 9, 8, 7, 9 }, each Number.IsOdd( _ ) )

For lists containing textual data, you might want to filter based on specific keywords or phrases. For instance, to identify the latest comments in a feedback list that mention the word “too”, regardless of case, you can use Text.Contains in combination with Comparer.OrdinalIgnoreCase

// Output: : { "Too slow", "Crashes too often" }
List.LastN( 
  { "Great app", "Not user-friendly", "Too slow", "Crashes too often" }, 
  each Text.Contains(_, "Too", Comparer.OrdinalIgnoreCase)
)

Learn more about List.LastN in the following articles:

Other functions related to List.LastN are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-lastn

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy