List.FirstN

Updated on
Rick de Groot

List.FirstN is a Power Query M function that returns the first N items in a list based on either a number or a condition. The function returns a list of items either up to a specified number or all items that meet a condition until one fails.

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

Syntax

List.FirstN(
   list as list,
   countOrCondition as any,
) as any

Description

The List.FirstN function is designed to extract the initial ‘N’ items from a list. Users can specify a count to determine how many items to retrieve or provide a condition to filter the results.

  • Count: If a number is specified, up to that many items are returned.
  • Condition: When a condition is used, the function fetches items from the start of the list until it encounters a value that doesn’t meet the criteria.

The output is a new list containing the extracted items.

Examples

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

Retrieving FirstN Values

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

//  Output: { 500, 550, 520 }
List.FirstN( { 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 first two items from such a list:

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

List.FirstN can be useful to retrieve to Top 3 values in a column. The following table contains 19, 14, 14 and 9 as top rows.

List.FirstN Dataset

To return these 4 rows with the top 3 values you can use: 

let 
  Source = MyTable,
  DistinctValues = List.Distinct( Source[Result] )
  SortedValues = List.Sort( DistinctValues )
  Top3Values = List.FirstN( SortedValues , 3 )
  Result = Table.SelectRows( Source, each List.Contains( Top3Values, [Result] ) )
in
  Result

That shows an alternative to using the Table.AddRankColumn function.

Applying Custom Conditions

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

For instance, to fetch the initial positive numbers from a list, you can use:

// Output: {3, 4, 5}
List.FirstN( {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: { 3, 5 }
List.FirstN( { 3, 5, 4, 9, 8, 7 }, each Number.IsOdd( _ ) )

For lists containing textual data, you might want to filter based on specific keywords or phrases. For instance, to identify the initial 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.FirstN( 
  { "Too slow", "Crashes too often", "Great app", "Not user-friendly" }, 
  each Text.Contains(_, "Too", Comparer.OrdinalIgnoreCase)
)

Learn more about List.FirstN in the following articles:

Other functions related to List.FirstN are:

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

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