Table.SingleRow
TableReturns the single row of a table as a record. Throws an error if the table does not have exactly one row.
Syntax
Table.SingleRow(table as table) as recordParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | A table that must contain exactly one row. |
Return Value
record — The single row of the table as a record.
Remarks
Table.SingleRow asserts that a table contains exactly one row and returns that row as a record. If the table has zero rows or more than one row, an error is raised at runtime. This "fail loud" behavior makes it a deliberate assertion — use it when your query logic guarantees exactly one match and you want an explicit error rather than a silent null or the wrong row if the assumption is violated.
Typical use cases include: reading a unique configuration parameter after filtering by name, extracting a uniquely-identified record after a key lookup, or validating that a pre-filtered dataset contains exactly one result before processing it.
Choose Table.SingleRow over Table.First when the one-row constraint must be enforced. Choose Table.First with a default value when zero rows is an acceptable outcome. Choose Table.FirstValue when you only need the top-left scalar and don't need to guard against row count.
Examples
Example 1: Look up a specific customer by CustomerID
let
Customers = #table(
{"CustomerID","Name","Email","City","State"},
{{1,"Alice","alice@example.com","New York","NY"},
{2,"Bob","bob@example.com","Chicago","IL"},
{3,"Charlie","charlie@example.com","Houston","TX"},
{4,"Diana","diana@example.com","Seattle","WA"}}
),
Filtered = Table.SelectRows(Customers, each [CustomerID] = 3)
in
Table.SingleRow(Filtered)Result | |
|---|---|
| 1 | [CustomerID = 3, Name = "Charlie", Email = "charlie@example.com", City = "Houston", State = "TX"] |
Example 2: Read a configuration value using field access on the single row
let
Config = #table(
{"Parameter","Value"},
{{"DefaultRegion","East"},{"MaxOrders","500"},{"CurrencyCode","USD"}}
),
Filtered = Table.SelectRows(Config, each [Parameter] = "CurrencyCode")
in
Table.SingleRow(Filtered)[Value]Result | |
|---|---|
| 1 | USD |
Example 3: Assert a product lookup returns exactly one result
let
Products = #table(
type table [ProductID = number, ProductName = text, Category = text, Price = number, InStock = logical],
{{1,"Widget A","Widgets",25.00,true},{2,"Gadget B","Gadgets",50.00,true},
{3,"Widget C","Widgets",15.00,false},{4,"Gadget D","Gadgets",75.00,true}}
),
Filtered = Table.SelectRows(Products, each [ProductID] = 4)
in
Table.SingleRow(Filtered)[Price]Result | |
|---|---|
| 1 | 75 |