Table.ContainsAny

Updated on

Table.ContainsAny is a Power Query M function that checks if any 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 any record.

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

Syntax

Table.ContainsAny(
   table as table,
   rows as list,
   optional equationCriteria as any,
) as logical
ArgumentAttributeDescription
tableThe table to search.
rowsA list of record values to search for.
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.ContainsAny checks if any records in rows appear as rows in table. An optional equationCriteria can be used for comparison.

Examples

Determine if the table ( {[a = 1, b = 2], [a = 3, b = 4]} ) contains the rows [a = 1, b = 2] or [a = 3, b = 5].

// Output: true
Table.ContainsAny( 
    Table.FromRecords( {
        [a = 1, b = 2],
        [a = 3, b = 4]
    } ),
    {
        [a = 1, b = 2],
        [a = 3, b = 5]
    }
 )

Determine if the table ( {[a = 1, b = 2], [a = 3, b = 4]} ) contains the rows [a = 1, b = 3] or [a = 3, b = 5].

// Output: false
Table.ContainsAny( 
    Table.FromRecords( {
        [a = 1, b = 2],
        [a = 3, b = 4]
    } ),
    {
        [a = 1, b = 3],
        [a = 3, b = 5]
    }
 )

Determine if the table ( Table.FromRecords( {[a = 1, b = 2], [a = 3, b = 4]} ) ) contains the rows [a = 1, b = 3] or [a = 3, b = 5] comparing only the column [a].

// Output: true
Table.ContainsAny( 
    Table.FromRecords( {
        [a = 1, b = 2],
        [a = 3, b = 4]
    } ),
    {
        [a = 1, b = 3],
        [a = 3, b = 5]
    },
    "a"
 )

Other functions related to Table.ContainsAny are:

BI Gorilla Youtube Channel

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