A list is a collection of zero or more values, each of the same data type: a list of numbers, a list of names, a list of email addresses, a list of rows, etc.

A list expression is an expression that produces a list, or an expression that uses one or more lists to produce a result of any type.

Constructing Lists

Constructing a List from a Column of a Table

Referencing a table and column together (a table-column reference) constructs a list of all values in that column of that table. Note that if the column itself contains duplicate values, so will the list.

  • Fruits[Name] produces a list of all Name column values from the Fruits table. This is equivalent to SELECT(Fruits[name], TRUE, FALSE). See also SELECT().
  • Orders[Customer] produces a list of all Customer column values from the Orders table. Equivalent to SELECT(Orders[Customer], TRUE, FALSE).
  • Order Details[SKU] produces a list of all SKU column values in the Order Details table. Equivalent to SELECT(Order Details[SKU], TRUE, FALSE).

Constructing a List from a Column of Type List or EnumList

The value of a column of type List or EnumList is already a list, and may be used directly anywhere a list is allowed within an expression.

  • [Vacation Dates] gives the list of vacation dates from with the current row, e.g. in an Employees table.
  • [Notification Emails] gives the list of notification email addresses from the current row, e.g. in an Events table.
  • [Related Order Details] gives the list of Order Details rows related to the current row, e.g in an Orders table.

A table-column reference to a column of type List or EnumList will produce a list of lists. To "flatten" the list-of-lists into a single list composed of the values of the component lists, wrap the table-column reference with SPLIT().

  • SPLIT(Employees[Vacation Dates], " , ") produces a list of all employee vacation dates.
  • SPLIT(Events[Notification Emails], " , ") produces a list of all notification email addresses.

Constructing a List from Expressions

The LIST() function constructs a list from any combination of raw values, column values, and expressions. The values in the resulting list will be in the order they were given to the LIST() function.

  • LIST("Apple", "Banana", "Cherry") produces a list of Text values: Apple, Banana, Cherry.
  • LIST([Mobile Phone], [Office Phone], [Home Phone]) produces a list of phone numbers from three column values of the current row.
  • LIST(1, (1 + 1), (6 / 2), ROUND(POWER(2, 2)), ROUND(SQRT(25))) produces a list of Number values: 1, 2, 3, 4, 5. See also: POWER(), ROUND(), SQRT().
  • LIST() produces an empty list.

Constructing a List from a Function

A number of functions produce lists as their result:

Constructing a List from a List Dereference

A column value of type List or EnumList with a base type of Ref can be dereferenced to produce a new list of the values from dereferencing each individual reference, an operation called a list dereference.

A list dereference is performed by enclosing the Ref list column name (e.g., Related Orders) and the column name of the desired column value (e.g., Order Date) each in square brackets ([, ]) and placing them adjacent to each other:

[Related Orders][Order Date]

The result will be a list of Order Date column values from the rows identified by the list in the Related Orders column value. The result is similar to (assuming the referenced table is Orders and its key column is Order ID):

SELECT(
  Orders[Order Date],
  IN([Order ID], [_THISROW].[Related Orders])
)

Constructing a List from Raw Values

A list can be constructed from raw values by enclosing the raw values in curly braces ({, }). The list must have at least one value. Multiple raw values should be separated by a comma (,). All raw values within the list must be of the same data type.

  • { "Apple", "Banana", "Cherry" } produces a list of Text values: Apple, Banana, Cherry.
  • { 1, 2, 3 } produces a list of Number values: 1, 2, 3.

Within curly braces, expressions are loosely recognized but may be evaluated in unexpected ways or not evaluated at all. Using expressions within curly braces is strongly discouraged! To use a raw value that might be interpreted as an expression, enclose the raw value in double quotes (").

  • { [Mobile Phone], [Office Phone], [Home Phone] } produces a list of Text values: [Mobile Phone] , [Office Phone] , [Home Phone]. Because the raw values appear to be expressions, the expressions will be checked for validity and may produce errors (e.g., if a column doesn't exist), but these apparent expressions will not be evaluated!
  • { 1, (1 + 1), (6 / 2), ROUND(POWER(2, 2)), ROUND(SQRT(25)) }  produces a list of Number values: 1, 1, 1, 6, 2, 4, 5. Note that these expressions are recognized but do not produce the expected results! See also: POWER(), ROUND(), SQRT().
  • { "(1 + 1)", "(6 / 2)" } produces a list of Text values: (1 + 1), (6 / 2). Note the apparent expressions are not recognized as expressions within the quotes.

Constructing a List with Addition

The contents of two lists can be combined to construct a new list using the list-add operator (+). The values of the resulting list will be in the order they occurred in the original lists. Duplicate entries will be preserved.

  • ({ 1, 2, 3 } + LIST(2, 3, 4)) produces a list of Number values: 1, 2, 3, 2, 3, 4. See also LIST().
  • (Employees[Office Phone] + Employees[Home Phone]) produces a list of all employee office and home phone numbers.

The list resulting from list addition will adopt the data type of the left-side list. If the right-side list is of a different data type, this may change how the values taken from it are interpreted.

  • ({ 3 } + { 3.14 }) produces a list of Number values from a list of one Number value (3) and a list of one Decimal value (3.14).
  • (LIST() + { 3.14 }) produces a list of one Text value from an empty list (Text by default) and a list of one Decimal value (3.14)

Constructing a List with Subtraction

The list-subtract operator (-) will produce a new list with the values of the left-side list that are not present in the right-side list. The values of the resulting list will be in the order they occurred in the original left-side list. Duplicate result entries will be omitted.

  • ({ 1, 2, 3 } - LIST(2, 3, 4)) produces a list of one Number value: 1. See also LIST().
  • ({ "Bob", "Mary", "Bob", "Alice" } - { "Alice" }) produces a list of Text values: Bob, Mary. In addition to the requested removal of Alice, note the duplicate occurrence of Bob was also omitted from the result.
  • ({ "Bob", "Mary", "Bob", "Alice" } - LIST()) produces a list of Text values: Bob, Mary, Alice. Note that the duplicate occurrence of Bob was omitted from the result. Equivalent to UNIQUE({ "Bob", "Mary", "Bob", "Alice" }). See also UNIQUE().
  • ({ "Bob", "Mary", "Bob", "Alice" } - { "Bob" }) produces a list of Text values: Mary, Alice. Note that although Bob only occurs once in the right-side list, both (all) occurrences of Bob from the left-side list are removed in the result.

Using Lists

Using Lists in Functions

A variety of functions accept lists as input:

  • ANY() - One arbitrary item of a list.
  • AVERAGE() - Arithmetic average of list of numeric values.
  • COUNT() - Count of list items.
  • IN() - Is item in a list?
  • INDEX() - One specific item of a list.
  • INTERSECT() - List of items common to two lists.
  • ISBLANK() - Does the list have no items?
  • ISNOTBLANK() - Does the list have any items?
  • MAX() - Highest of a list of numeric or temporal values.
  • MIN() - Lowest  of a list of numeric or temporal values.
  • ORDERBY() - List of rows in custom order.
  • SELECT() - List of column values from select rows.
  • SORT() - List of items in order.
  • STDEVP() - Arithmetic standard deviation of a list of numeric values.
  • SUM() - Arithmetic sum of a list of numeric values.
  • TOP() - List of initial items of list.
  • UNIQUE() - List of items with duplicates removed.

Using Lists to Show and Hide Columns

A column's Show? expression may be used to conditionally show or hide a column from the user. The Show? expression must produce a Yes/No result, not a list, but lists are commonly used within Show? expressions.

  • IN(USEREMAIL(), SELECT(Users[Email], ("Admin" = [Role])))  shows the column only for users whose email (USEREMAIL()) is in the list of admin user emails (SELECT(...)). See also: IN(), SELECT(), USEREMAIL().
  • IN(CONTEXT("ViewType"), { "deck", "table" }) shows the column only if the current view type (CONTEXT(...)) in the list of view types ({...}). See also: CONTEXT(), IN().

Using Lists to Show and Hide Views

A view's Show if expression may be used to conditionally show or hide a view in the main menu or navigation bar. The Show if expression must produce a Yes/No result, not a list, but lists are commonly used within Show if expressions.

  • IN(USEREMAIL(), SELECT(Users[Email], ("Admin" = [Role]))) shows the view only for users whose email (USEREMAIL()) is in the list of admin user emails (SELECT(...)). See also: IN(), SELECT(), USEREMAIL()

Using Lists to Suggest Column Values

A column's Suggested Values expression may be used to suggest values when the user goes to make a change to the column. The Suggested Values expression must produce a list, the values of which are then presented in a dropdown menu from which the user may choose.

  • Customers[Name] produces a list of existing customer names, allowing the user to select from the list. Note that the list will be unsorted; to provide a sorted list, use SORT(Customers[Name]). See also SORT().
  • LIST(TODAY(), (TODAY() + 1), (TODAY() + 2) produces a list of the dates for today, tomorrow, and the day after. See also: LIST(), TODAY().

Using Lists to Validate Column Values

The Valid If expression may be used to validate a column value. If the Valid If expression produces a list result, the values in the list are considered the only valid values for the column. When the user attempts to change the column's value, the values of the list are presented in a dropdown menu for the user to choose from.

  • Customers[Name] produces a list of existing customer names, allowing the user to select from the list. Note that the list will be unsorted; to provide a sorted list, use SORT(Customers[Name]). See also SORT().
  • { "Ordered", "Prepared", "Shipped", "Delivered" } produces a fixed list of order status values from which the user may choose. Note that  the defined values of an Enum column could also be used to offer the same list of fixed options.
Did this answer your question?