Table.ContainsAll

D

Back to top

T

Back to top

#

Back to top

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

The M language is a functional programming language that is used to define queries and transformations in Power Query. It allows you to manipulate data in a powerful and flexible way. In this article, we will explore the M code behind the Power Query M function Table.ContainsAll.

What is Table.ContainsAll?

Table.ContainsAll is a M function in Power Query that is used to check if a table contains all the values in a given list. It returns true if all the values are found in the table, and false otherwise. The syntax of the Table.ContainsAll function is as follows:

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

The first parameter is the table that you want to check, the second parameter is the list of values that you want to check for, and the third parameter is an optional equation criteria that you can use to specify the comparison method.

How does Table.ContainsAll work?

The Table.ContainsAll function works by iterating over the list of values and checking if each value is present in the table. If at least one value is not found, it returns false. However, you can customize the behavior of the function by using the equation criteria parameter.

The equation criteria parameter allows you to specify the comparison method that should be used to match the values in the table. By default, the function uses the equality operator (=) to compare the values. However, you can use other comparison operators such as <=, >=, <, >, and <>.

You can also use a predicate function as the equation criteria parameter. A predicate function is a function that takes two arguments and returns a logical value. It can be used to implement custom comparison logic.

Examples of using Table.ContainsAll

Let’s take a look at some examples of how to use the Table.ContainsAll function in Power Query.

Example 1: Basic usage

Suppose we have a table called “Sales” that contains data about sales transactions. We want to check if the table contains all the product names in a list called “ProductList”. We can use the following M code:

let

Sales = Table.Load("Sales"),

ProductList = {"Product A", "Product B", "Product C"},

ContainsAll = Table.ContainsAll(Sales, ProductList)

in

ContainsAll

This code loads the “Sales” table, defines the “ProductList” as a list of product names, and uses the Table.ContainsAll function to check if the table contains all the products.

Example 2: Using the equation criteria parameter

Suppose we have a table called “Customers” that contains data about customers. We want to check if the table contains all the customers whose age is greater than or equal to 18. We can use the following M code:

let

Customers = Table.Load("Customers"),

AgeList = {18},

ContainsAll = Table.ContainsAll(Customers, AgeList, (x, y) => x >= y)

in

ContainsAll

This code loads the “Customers” table, defines the “AgeList” as a list of age values, and uses the Table.ContainsAll function to check if the table contains all the customers whose age is greater than or equal to 18.

In this article, we explored the M code behind the Power Query M function Table.ContainsAll. We learned that Table.ContainsAll is a powerful function that allows you to check if a table contains all the values in a given list. We also learned how to use the equation criteria parameter to customize the behavior of the function. By using Table.ContainsAll and other M functions in Power Query, you can perform complex data transformations and prepare your data for analysis and visualization.

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)