Table.NestedJoin

In this article, we will explore the M code behind the Table.NestedJoin function and provide examples of how it can be used to solve real-world data problems.

What is Table.NestedJoin?

Table.NestedJoin is a function in Power Query M that allows you to join two tables together based on a common column or set of columns. The difference between Table.NestedJoin and traditional SQL join is that it can handle more complex data structures, such as nested tables and lists.

Table.NestedJoin takes four arguments:

– The first argument is the first table you want to join.

– The second argument is the column or columns you want to join on.

– The third argument is the second table you want to join.

– The fourth argument is the join kind, which specifies how the tables should be joined.

The M Code Behind Table.NestedJoin

To understand how Table.NestedJoin works, let’s take a look at the M code behind the function.

The first argument, `table1`, is the first table you want to join. This can be any table in your Power Query workspace.

The second argument, `joinColumn`, is the column or columns you want to join on. This can be a single column name, a list of column names, or a record that defines the join keys.

The third argument, `table2`, is the second table you want to join. This can be any table in your Power Query workspace.

The fourth argument, `joinKind`, is the join kind, which specifies how the tables should be joined. There are four options for join kind:

– 0 (InnerJoin): Returns only the rows that have matching values in both tables.

– 1 (LeftOuterJoin): Returns all the rows from the left table and the matching rows from the right table. If there is no match, the right-side columns will contain null values.

– 2 (RightOuterJoin): Returns all the rows from the right table and the matching rows from the left table. If there is no match, the left-side columns will contain null values.

– 3 (FullOuterJoin): Returns all the rows from both tables. If there is no match, the missing values will be filled with null values.

Examples of Using Table.NestedJoin

Let’s take a look at some real-world examples of how Table.NestedJoin can be used to solve data problems.

Example 1: Joining Tables Based on Multiple Columns

Suppose you have two tables, Sales and Customers. Sales table contains the following columns: OrderID, CustomerID, ProductID, and Quantity, while Customers table contains the following columns: CustomerID, FirstName, LastName, and Email.

You want to join these two tables based on both CustomerID and ProductID columns. To do this, you can use the following M code:

This code will return a new table that combines the data from the Sales and Customers tables based on the two common columns.

Example 2: Joining Tables with Nested Tables

Suppose you have two tables, Orders and Products. Orders table contains the following columns: OrderID, CustomerID, and Products, while Products table contains the following columns: ProductID, ProductName, and Price.

The Products column in the Orders table is a nested table that contains the ProductID and Quantity for each product ordered. You want to join these two tables based on the ProductID column.

To do this, you can use the following M code:

This code will return a new table that combines the data from the Orders and Products tables based on the ProductID column in the nested Products table.

Table.NestedJoin is a powerful tool in Power Query M that allows you to join tables together based on a common column or set of columns, including nested tables and lists. By understanding the M code behind the function and using real-world examples, you can use Table.NestedJoin to solve complex data problems and streamline your data analysis workflow.

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

Upcoming Courses

Contact Us