Table.MatchesAllRows

Table.MatchesAllRows is a function that compares two tables and returns all the rows in the first table that match all the rows in the second table. This function is particularly useful when you need to merge two tables based on multiple columns. In this article, we will take a closer look at the M code behind Table.MatchesAllRows and how to use it effectively.

The Syntax of Table.MatchesAllRows

The syntax of Table.MatchesAllRows is straightforward and easy to understand. It takes two parameters: the first parameter is the table to search, and the second parameter is the table to match. Here is the basic syntax of Table.MatchesAllRows:

Table.MatchesAllRows returns a table that contains all the rows in table1 that match all the rows in table2. The matching is based on the column values in both tables, and the order of the columns does not matter.

The M Code Behind Table.MatchesAllRows

The M code behind Table.MatchesAllRows is not complicated, but it is essential to understand how it works to use it effectively. Here is the M code behind Table.MatchesAllRows:

The code above consists of two basic steps: matching and filtering. Let’s break down each step:

Matching

The first step in Table.MatchesAllRows is to join the two tables based on the key columns. The key columns are the columns that are used to match the two tables. In the M code above, the variable `key_columns` represents the key columns. You can define the key columns based on your needs.

The above code uses the Table.NestedJoin function to join the two tables. The function takes four parameters: the first parameter is the table1 to join, the second parameter is the key columns in table1, the third parameter is table2 to join, and the fourth parameter is the key columns in table2. The “matched” parameter is the name of the new column that is created to hold the matching rows.

Filtering

The second step in Table.MatchesAllRows is to filter the matched rows to return only the rows that match all the rows in table2.

The above code uses the Table.SelectRows function to filter the matched rows. The function takes two parameters: the first parameter is the table to filter, and the second parameter is a function that defines the filter criteria. In the above code, the filter criteria are defined using the List.Contains function, which checks if the key columns in table2 match any of the rows in the matched column.

Using Table.MatchesAllRows

Now that you understand the M code behind Table.MatchesAllRows, it is time to learn how to use it effectively. Here are some tips for using Table.MatchesAllRows:

Define the Key Columns

Before using Table.MatchesAllRows, define the key columns that you want to use to match the two tables. The key columns can be single or multiple columns, depending on your needs.

Sort the Tables

Table.MatchesAllRows does not sort the tables before matching them. Therefore, it is essential to sort the tables based on the key columns to ensure that the matching is accurate.

Remove Duplicates

If there are duplicate rows in either table, Table.MatchesAllRows may return unexpected results. Therefore, it is essential to remove duplicates from both tables before using the function.

Table.MatchesAllRows is a useful M function in Power Query that enables users to merge two tables based on multiple columns. Understanding the M code behind the function is essential to use it effectively. By following the tips outlined in this article, you can use Table.MatchesAllRows to transform and prepare your data efficiently.

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

Upcoming Courses

Contact Us