Table.ContainsAll
TableReturns true if the table contains all of the specified rows.
Syntax
Table.ContainsAll(table as table, rows as list, optional equationCriteria as any) as logicalParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to search. |
rows | list | Yes | A list of records, each representing a row that must exist in the table. |
equationCriteria | any | No | Optional. Column name(s) or comparer to control which fields are compared. |
Return Value
logical — true if every record in the rows list has a matching row in the table; false otherwise.
Remarks
Table.ContainsAll returns true only if every record in the rows list has at least one matching row in the table. It is the logical AND of multiple Table.Contains checks: all specified rows must be present. If any single row from the list is missing from the table, the function returns false.
This is useful for data quality validation — for example, asserting that a dimension table includes all required reference values before proceeding with a transformation, or confirming that a set of mandatory product IDs all appear in a lookup.
The equationCriteria parameter restricts which columns participate in the comparison, enabling partial record matching. An empty rows list returns true by vacuous truth. Like Table.Contains, this function does not fold to data sources.
Examples
Example 1: Verify all required customers have placed orders
let
Sales = #table(
{"OrderID", "CustomerName"},
{{1,"Alice"},{2,"Bob"},{3,"Charlie"},{4,"Alice"},{5,"Diana"}}
),
RequiredCustomers = {[CustomerName = "Alice"], [CustomerName = "Diana"]}
in
Table.ContainsAll(Sales, RequiredCustomers, "CustomerName")Result | |
|---|---|
| 1 | TRUE |
Example 2: Detect a missing required category
let
Products = #table(
{"ProductName", "Category"},
{{"Widget A","Widgets"},{"Gadget B","Gadgets"},{"Thingamajig E","Misc"}}
),
RequiredCategories = {[Category = "Widgets"], [Category = "Gadgets"], [Category = "Electronics"]}
in
Table.ContainsAll(Products, RequiredCategories, "Category")Result | |
|---|---|
| 1 | FALSE |
Example 3: Validate all mandatory OrderIDs exist before processing
let
Sales = #table(
{"OrderID", "CustomerName", "Product"},
{{1,"Alice","Widget A"},{2,"Bob","Gadget B"},{3,"Charlie","Widget C"}}
),
MandatoryOrders = {[OrderID = 1], [OrderID = 2], [OrderID = 3]}
in
Table.ContainsAll(Sales, MandatoryOrders, "OrderID")Result | |
|---|---|
| 1 | TRUE |