TOP()

Returns a list that contains the specified number of items at the beginning of a list. If the number of list items to be returned is greater than the length of the list, returns the full list. If the number of list items to be returned is less than 1, returns an empty list.

Sample usage

TOP(LIST("Red", "Yellow", "Green"), 2) returns a list of 2 items: Red, Yellow

TOP({"Red", "Yellow", "Green"}, 4) returns a list of 3 items: Red, Yellow, Green

Days of the month

A list of days of a month, accounting for the difference in months:

TOP(
  {
    1, 2, 3, 4, 5, 6, 7, 8, 9,
    10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
    20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
    30, 31
  },
  EOMONTH([Date], 0)
)

1. {1, 2, 3, ..., 31} constructs a list of possible month day numbers.
2. EOMONTH([Date], 0) identifies the number of the last day of the month specified by the Date (or DateTime) value in the Date column.
3. TOP(..., ...) returns a list of only the day numbers from 1 to the last day of the given month.

See also: EOMONTH()

Lowest values

The 5 earliest employee hire dates:

TOP(SORT(Employees[Hire Date]), 5)

1. Employees[Hire Date] retrieves the list of all values from the Hire Date column of the Employees table.
2. SORT(...) orders the list of dates chronologically in ascending/low-to-high order (the default sort order), putting the earliest dates at the beginning.
3. TOP(..., 5) returns the first 5 values from the sorted list, the 5 earliest hire dates.

See also: SORT()

Rows with highest values

The rows of the 3 students with the highest GPAs in Mr Sandwich's class:

TOP(
  ORDERBY(
    FILTER("Students",
      AND(
        ISNOTBLANK([Teacher]),
        ([Teacher] = "Mr Sandwich")
      )
    ),
    [GPA], TRUE
  ),
  3
)

1. FILTER("Students", ...) returns a list of key values from the Students table that match a condition.
2. AND(..., ...) limits the filter to only those rows that match all of the given sub-conditions.
3. ISNOTBLANK([Teacher]) requires the Teacher column value not be blank.
4. [Teacher] = "Mr Sandwich" requires the Teacher column value be exactly Mr Sandwich.
5. ORDERBY(..., [GPA], TRUE) orders the filtered keys by the values of their corresponding GPA column value in descending/high-to-low order (TRUE), putting high GPAs first.
6. TOP(..., 3) returns the first 3 items in the ordered list, the keys of the rows having the 3 highest GPAs.

See also: AND(), FILTER(), ISNOTBLANK(), ORDERBY()

Syntax

TOP(list, how-many)

• list - List of any type.
• how-many - A Number value that specifies the number of item values from list to be returned.

See also

ANY()

INDEX()