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])
)

Sorting by Another Column

To gather a list of column values sorted by a different column value, use the App formula expression of a column to generate a list of row references in the desired order.

For example, for a column named Next Two Weeks, gather the list of rows from the Events table with a Date within the next 14 days, and order the rows by Date from earliest to latest:

ORDERBY(
FILTER(
"Event",
([Date] < (TODAY() + 14))
),
[Date]
)

A list dereference can then be used to get the desired list of column values in that same order:

[Next Two Weeks][Event Name]

The resulting list of dereferenced values will be in the same order as the original reference list.

This approach is commonly used to get lists for reports.

See also: Date & Time Expressions, FILTER(), ORDERBY(), TODAY()

See Also

Dereference Expressions, List Expressions, SELECT()

Did this answer your question?