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

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

If you want to grab just the first 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.FirstN with five practical examples.

Table of Contents

Syntax of List.FirstN Function

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

where

list (required, list). The list to take items from.
countOrCondition (required, any). Either a number or a condition. Give it a number and you get up to that many items from the start of the list. Give it a condition (an each function) and it keeps taking items until one fails the test.

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

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

Example 1: Get the first N items with a count

Say you run a workshop with 2 seats and a 4-person waitlist. The first 2 names get in.

let
    Source = List.FirstN({"Harriet Cole","Dev Patel","Mona Reyes","Felix Grant"},2)
in
    Source

Result: {"Harriet Cole","Dev Patel"}

The count 2 takes the first two items in list order. Everything after them is ignored.

Example 2: Ask for more items than the list has

Here we ask for 10 items from a list of only 3 document codes.

let
    Source = List.FirstN({"REF-218","REF-219","REF-220"},10)
in
    Source

Result: {"REF-218","REF-219","REF-220"}

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 3: Use a condition to take items until one fails

These are hourly temperature readings from a machine. You want every reading before the first one that hits 75 or more.

let
    Source = List.FirstN({62,64,67,71,93,68,65},each _<75)
in
    Source

Result: {62,64,67,71}

The reading 93 is the first item that fails each _<75, so the function stops there.

The later readings 68 and 65 would pass the test, but they are never even checked. This is the part that trips people up. List.FirstN is not a filter, it only takes items while the condition holds.

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

Example 4: Grab leading header lines with a text condition

Exported files often start with a few comment lines before the real data. In this list, the header lines all begin with #.

let
    Source = List.FirstN({"# Export: sensor log","# Plant: Lakeside","reading_id|value","R-1043|66"},each Text.StartsWith(_,"#"))
in
    Source

Result: {"# Export: sensor log","# Plant: Lakeside"}

The condition can be any each function, not just a number comparison. Here Text.StartsWith keeps taking lines until the first one that does not begin with #.

Anything that returns true or false works, even a membership check with List.Contains.

Example 5: Get the top 3 values from a table column

Say you have a Campaigns table and want the 3 highest click counts.

Here is the starting data:

Campaign	Clicks
Spring Sale	1840
Newsletter	920
Retargeting	2650
Brand Search	1310
Social Push	760

Sort the column in descending order first, then take the first 3 values:

let
    Source = Excel.CurrentWorkbook(){[Name="Campaigns"]}[Content],
    Sorted = List.Sort(Source[Clicks],Order.Descending),
    Top3 = List.FirstN(Sorted,3)
in
    Top3

Result: {2650,1840,1310}

Source[Clicks] turns the column into a list. List.Sort with Order.Descending puts the biggest values first, and List.FirstN takes the top 3.

Things to keep in mind with List.FirstN

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.First when you want the item itself.
A condition that fails on the very first item returns {}. There is no error, just an empty list. If you expected matches, check whether the condition holds at the start of the list.
Passing a whole table throws Expression.Error: We cannot convert a value of type Table to type List. Pull out one column as a list first with Source[Column], like Example 5 does.
List.LastN is the mirror function. It takes items from the end of the list instead, with the same count or condition options.

Common questions about List.FirstN

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

Table.FirstN keeps whole rows of a table. List.FirstN returns values from a single list, such as one column pulled out with Source[Column]. And if you want every matching row rather than just the leading run, filter with Table.SelectRows.

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

List.FirstN always starts at the beginning of the list. List.Range lets you start at any position, so you can grab items 4 through 6. It also errors on an out-of-range offset, while List.FirstN happily returns a shorter list.

List of All Power Query Functions

Related Power Query Functions / Articles: