Table.MatchesAllRows
TableReturns true if every row in the table satisfies the given condition function.
Syntax
Table.MatchesAllRows(table as table, condition as function) as logicalParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table whose rows are tested. |
condition | function | Yes | A function that accepts a row as a record and returns a logical value. |
Return Value
logical — true if the condition function returns true for every row; false otherwise.
Remarks
Table.MatchesAllRows applies a predicate function to every row of a table and returns true only if every row satisfies the condition. The function short-circuits — it returns false as soon as the first non-matching row is found, without evaluating remaining rows. An empty table returns true by vacuous truth.
This function is ideal for data quality gate checks embedded in a query pipeline. Common uses include asserting that all key columns are non-null before a join, that all numeric values fall within an expected range, or that all required fields are populated. It reads more clearly than List.AllTrue(List.Transform(Table.ToRows(t), each condition)) and is more efficient due to short-circuiting.
Table.MatchesAllRows does not fold to data sources. For large database tables, validate constraints at the source using aggregations (e.g., MIN, COUNT WHERE ... IS NULL) rather than pulling all rows locally.
Examples
Example 1: Confirm all products have a positive price
let
Products = #table(
type table [ProductID = number, ProductName = text, Price = number, InStock = logical],
{{1,"Widget A",25.00,true},{2,"Gadget B",50.00,true},{3,"Widget C",15.00,false},{4,"Gadget D",75.00,true},{5,"Thingamajig E",120.00,false}}
)
in
Table.MatchesAllRows(Products, each [Price] > 0)The final output — every row satisfies [Price] > 0 (the lowest price is 15), so Table.MatchesAllRows returns true.
Result | |
|---|---|
| 1 | TRUE |
Example 2: Detect rows with a null or missing customer name
let
Sales = #table(
{"OrderID", "CustomerName", "Product"},
{{1,"Alice","Widget A"},{2,null,"Gadget B"},{3,"Charlie","Widget C"}}
)
in
Table.MatchesAllRows(Sales, each [CustomerName] <> null)The final output — order 2 fails the [CustomerName] <> null test, so Table.MatchesAllRows returns false.
Result | |
|---|---|
| 1 | FALSE |
Example 3: Assert all salaries are within an acceptable range
let
Employees = #table(
type table [EmployeeID = text, FullName = text, Department = text, Salary = number],
{{"E001","alice smith","Sales",55000},{"E002","BOB JONES","Engineering",95000},
{"E003","Charlie Brown","Marketing",72000},{"E005","Eve Martinez","Sales",88000}}
)
in
Table.MatchesAllRows(Employees, each [Salary] >= 30000 and [Salary] <= 200000)The final output — every salary falls between 30000 and 200000, so Table.MatchesAllRows returns true.
Result | |
|---|---|
| 1 | TRUE |
Compatibility
