Table.ContainsAll

Updated on

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
ArgumentAttributeDescription
tableThe table to search.
rowsThe row values to look for, specified as a list of records.
equationCriteriaoptionalUses 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"]
    }
 )

Other functions related to Table.ContainsAll are:

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