Table.ContainsAny

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.ContainsAny

Overview of Table.ContainsAny Function

The Table.ContainsAny function is used to check if any of the values in a given list are present in a column of a table. It returns a Boolean value of True if at least one of the values in the list is found in the column, and False otherwise. The syntax of the Table.ContainsAny function is:

Table.ContainsAny(table as table, values as list, columns as list, comparer as nullable function) as logical

The arguments of the Table.ContainsAny function are:

table: The table to be searched.

values: A list of values to search for.

columns: A list of columns to search in.

comparer: An optional argument that specifies the comparison method to be used when searching for values.

The M Code Behind Table.ContainsAny Function

The M code behind the Table.ContainsAny function is a combination of several other M functions, including List.ContainsAny, Table.SelectColumns, and List.Intersect. The following is the M code for the Table.ContainsAny function:

(Table as table, values as list, columns as list, optional comparer as nullable function) =>

let

SelectColumns = Table.SelectColumns(Table, columns),

Intersection = List.Intersect(SelectColumns{0}, values, comparer),

Result = List.Count(Intersection) > 0

in

Result

The M code starts with the let statement, which declares variables that will be used in the function. The SelectColumns variable uses the Table.SelectColumns function to select only the columns specified in the columns list. The Intersection variable uses the List.Intersect function to find the intersection of the values in the selected column and the values in the values list, using the optional comparer function if it is provided. The Result variable uses the List.Count function to count the number of values in the intersection list, and checks whether it is greater than 0. If it is, the function returns True, otherwise it returns False.

Examples of Using Table.ContainsAny Function

The Table.ContainsAny function can be used in various scenarios to search for values in a table column. Here are some examples:

Example 1: Finding Rows With Specific Values

Suppose you have a table of sales data, and you want to find all the rows where the Product column contains any of the values “Apple”, “Banana”, or “Orange”. You can use the following formula:

Table.ContainsAny(Sales, {"Apple", "Banana", "Orange"}, {"Product"})

This will return a table that contains only the rows where the Product column contains any of the specified values.

Example 2: Filtering Rows Based on Multiple Columns

Suppose you have a table of customer data, and you want to find all the rows where the Name column contains “John” and the City column contains “New York”. You can use the following formula:

Table.ContainsAny(Customers, {"John"}, {"Name"}) and Table.ContainsAny(Customers, {"New York"}, {"City"})

This will return a table that contains only the rows where the Name column contains “John” and the City column contains “New York”.

Example 3: Using Custom Comparison Function

Suppose you have a table of product data, and you want to find all the rows where the Name column contains any of the values “Apple”, “Banana”, or “Orange”, but you want to ignore the case of the text. You can use the following formula:

Table.ContainsAny(Products, {"Apple", "Banana", "Orange"}, {"Name"}, (x, y) => Text.Lower(x) = Text.Lower(y))

This will return a table that contains only the rows where the Name column contains any of the specified values, ignoring the case of the text.

The Table.ContainsAny function is a powerful M function that helps to search for values in a table column. It is a combination of several other M functions, and its M code can be customized to suit different scenarios. By understanding the M code behind the Table.ContainsAny function, you can use it more effectively and efficiently in your Power Query workflows.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)