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:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy