Table.ContainsAll

Table.ContainsAll is a Power Query M function that checks if all specified records in a list appear as rows in the table, with an optional parameter to control comparison between rows. The function returns a logical value indicating the presence of all records.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.ContainsAll(
   table as table,
   rows as list,
   optional equationCriteria as any,
) as logical

Argument	Attribute	Description
table		The table to search.
rows		The row values to look for, specified as a list of records.
equationCriteria	optional	Uses Comparer Functions to determine how values are equated during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

Description

Table.ContainsAll checks if all records in rows appear as rows in table. An optional equationCriteria can be used for comparison.

Examples

Determine if the table contains all the rows, comparing only the column [CustomerID].

// Output: true
Table.ContainsAll( 
    Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    } ),
    {
        [CustomerID = 1, Name = "Bill"],
        [CustomerID = 2, Name = "Fred"]
    },
    "CustomerID"
 )

Determine if the table contains all the rows.

// Output: false
Table.ContainsAll( 
    Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    } ),
    {
        [CustomerID = 1, Name = "Bill"],
        [CustomerID = 2, Name = "Fred"]
    }
 )

Related functions

Other functions related to Table.ContainsAll are:

• Table.Contains
• Table.ContainsAny

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-containsall