Table.Contains

What is the Table.Contains Function?

The Table.Contains function is a M function in Power Query that checks if a table contains a certain value or not. It returns a Boolean value (true or false) depending on whether the value is present in the table or not.

The syntax for the Table.Contains function is as follows:

– `table`: The table to check for the value.

– `compareCriteria`: The value to check for in the table.

– `equationCriteria`: (Optional) A function that defines how to compare the value with the table.

Understanding the M code behind the Table.Contains Function

To understand the M code behind the Table.Contains function, let’s take a look at an example. Consider the following table:

| Name | Age | Gender |

|——-|—–|——–|

| John | 30 | Male |

| Jane | 35 | Female |

| Peter | 40 | Male |

Suppose we want to check if the “Name” column contains the value “John”. We can do this using the following M code:

Let’s break down this code step-by-step:

1. We start by creating a new query from the table using the “FromRows” function. This function takes a list of rows and converts it to a table.

2. Next, we use the Table.Contains function to check if the “Name” column contains the value “John”. We pass the “Source” table as the first argument, “John” as the second argument, and a custom function as the third argument. This custom function compares the value in the “Name” column with the value we are looking for.

3. Finally, we return the result of the Table.Contains function as our output.

Using the Table.Contains Function in Power Query

Now that we understand the M code behind the Table.Contains function, let’s see how we can use it in Power Query. Suppose we have a table with the following data:

| Product | Category | Price |

|———|———-|——-|

| Apple | Fruit | 2.00 |

| Orange | Fruit | 1.50 |

| Carrot | Vegetable| 1.00 |

Suppose we want to check if the “Product” column contains the value “Apple”. We can do this using the following steps:

1. Select the “Product” column and click on the “Add Column” tab in the ribbon.

2. Click on the “Custom Column” button.

3. In the “Custom Column” dialog box, enter a name for the new column (e.g. “ContainsApple”).

4. In the “Custom Column” dialog box, enter the following formula:

5. Click on the “OK” button to create the new column.

6. The new column will contain a Boolean value indicating whether the “Product” column contains the value “Apple” or not.

We can also use the Table.Contains function with multiple criteria. Suppose we want to check if the “Product” column contains the value “Apple” and the “Price” column is greater than 1. We can do this using the following formula:

This formula checks if the “Product” column contains the value “Apple” and the “Price” column is greater than 1. We use a record to specify the criteria and a custom function to compare the values.

In this article, we took a closer look at the M code behind the Table.Contains function in Power Query. We also saw how we can use this function to check if a column contains a certain value or not. By understanding the M code behind this function, we can customize it to meet our specific needs and make our data analysis more efficient.

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

Upcoming Courses

Contact Us