List.SingleOrDefault
ListReturns the single element of a list, or a default value if the list is empty. Throws an error if the list has more than one element.
Syntax
List.SingleOrDefault(list as list, optional default as nullable any) as anyParameters
| Name | Type | Required | Description |
|---|---|---|---|
list | list | Yes | The list expected to contain zero or one elements. |
default | nullable any | No | The value to return if the list is empty. Defaults to null if not specified. |
Return Value
any — The single item in the list, the default value if the list is empty, or an error if more than one item exists.
Remarks
List.SingleOrDefault is a null-safe version of List.Single. It returns the single element from the list if exactly one exists, or returns the default value (defaulting to null if omitted) if the list is empty. If the list has more than one element, it still throws an error — the "or default" only applies to the empty-list case.
This is the right choice for lookups where no match is valid and should return a fallback value, but multiple matches indicate a data quality issue that should surface as an error. It documents intent clearly: "I expect zero or one results; more than one is a bug."
- If default is omitted, an empty list returns null.
- A list with more than one element throws: Expression.Error: There were too many elements in the enumeration.
- To silently take the first of multiple results, use List.First with a default parameter.
- To require exactly one result (no empty allowed), use List.Single.
Examples
Example 4: Safe lookup with fallback
let
Prices = #table({"Product", "Price"}, {{"Widget", 9.99}, {"Gadget", 24.99}}),
LookupProduct = "Thingamajig",
MatchedPrice = List.SingleOrDefault(
Table.SelectRows(Prices, each [Product] = LookupProduct)[Price],
0
)
in
MatchedPriceThe final output — the fallback value 0, returned because "Thingamajig" was not found in the Prices table.
Result | |
|---|---|
| 1 | 0 |