Table.ContainsAll Function in Power Query

The Table.ContainsAll function in Power Query checks whether all the specified records exist in a given table. It returns a logical value: true if all records are present, false otherwise.

Syntax

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

The function has the following parameters:

table: The target table to search within.
rows: A list of records (rows) to check for presence in the table.
equationCriteria: It is an optional parameter. It may be specified to control comparison between the rows of the table. Custom criteria for comparison (e.g., column name list or comparer function). If not specified, all columns of the second argument are tested in the given table.

Example: Only check presence based on a subset of fields, ignoring others (e.g., check if all employee IDs exist, regardless of name or department).

Power Query M

let
    EmployeeTable = Table.FromRecords({
        [ID = 1, Name = "Ashish", Dept = "HR"],
        [ID = 2, Name = "Katrina", Dept = "Finance"],
        [ID = 3, Name = "Alia", Dept = "IT"]
    }),

    RequiredIDs = {
        [ID = 2, Name = "Harish"],
        [ID = 3, Name = "Mohan"]
    },

    Result = Table.ContainsAll(EmployeeTable, RequiredIDs, {"ID"})
in
    Result

The output of the above code is true.

Example: Determine if the table contains all the rows.

Power Query M

let
    EmployeeTable = Table.FromRecords({
        [ID = 1, Name = "Ashish", Dept = "HR"],
        [ID = 2, Name = "Katrina", Dept = "Finance"],
        [ID = 3, Name = "Alia", Dept = "IT"]
    }),

    RequiredRows = {
        [ID = 2, Name = "Harish"],
        [ID = 3, Name = "Mohan"]
    },

    Result = Table.ContainsAll(EmployeeTable, RequiredRows)
in
    Result

The output of the above code is false.

Previous Next