List.FirstN
Returns the first few items of a list, either a set number or all the items at the start that pass a test.
List.FirstN(list, countOrCondition)Returns: A list with the selected items.
What it does
List.FirstN takes the start of a list. You can ask for a fixed count, such as the first ten items, or pass a condition and it keeps items from the start for as long as the condition holds. Use it for a top-ten list after sorting, or for the opening rows of a period before a change in pattern.
Syntax and arguments
list: the list to read from.countOrCondition: either a whole number (how many to take) or a function (each ...) that is tested from the start and stops at the first item that fails.
Example
A controller wants the five largest vendor balances for the review pack. The list has already been sorted from largest to smallest.
let
VendorBalances = {98000, 74500, 61200, 40100, 36800, 22000, 9100},
Top5 = List.FirstN(VendorBalances, 5)
in
Top5
Top5 is the first five items: {98000, 74500, 61200, 40100, 36800}. The list must already be in descending order, because FirstN takes what comes first.
Example: a second, different use
Taking the opening run of values that are still above a threshold: List.FirstN(Balances, each _ >= 10000) keeps items from the start until the first one below 10,000.
Things to watch
- FirstN takes from the front of the list. To get the largest values, sort in descending order first.
- The condition form stops at the first failure. Items later in the list that pass the test are not included.
- If you ask for more items than the list has, you get the whole list, not an error.
- For a table, use Table.FirstN, which works on rows.
In business terms
Top-ten customers, largest expenses, earliest open items and the first few lines of a report are typical uses. Sorting first and then taking the top N is the usual pattern in a management report.