Table.AddJoinColumn

The M code behind this function is what makes it so powerful. Let’s take a closer look at how it works.

Basic Syntax of Table.AddJoinColumn

The basic syntax of `Table.AddJoinColumn` is as follows:

Here’s what each argument means:

– `table1`: The first table to join.

– `column1`: The column in `table1` to join on.

– `table2`: The second table to join.

– `column2`: The column in `table2` to join on.

– `joinKind`: An optional parameter that specifies the type of join to perform (e.g. inner join, left outer join, etc.).

– `joinAlgorithm`: An optional parameter that specifies the algorithm to use for joining the tables.

Joining Tables with Table.AddJoinColumn

To join two tables using `Table.AddJoinColumn`, you need to specify the two tables and the columns to join on. For example, let’s say we have two tables: `Customers` and `Orders`. The `Customers` table contains information about customers, such as their name and address, while the `Orders` table contains information about the orders they have made.

To join these tables on the `CustomerID` column, we would use the following M code:

This will create a new table that combines the columns from both tables, based on the `CustomerID` column.

Types of Joins

As mentioned earlier, `Table.AddJoinColumn` supports different types of joins. Here are some of the most common ones:

Inner Join

An inner join returns only the rows that have matching values in both tables. To perform an inner join using `Table.AddJoinColumn`, you need to specify the `joinKind` argument as `1`. For example:

Left Outer Join

A left outer join returns all the rows from the left table (`table1`), and the matching rows from the right table (`table2`). If there are no matching rows in the right table, the result will contain null values for the columns from the right table. To perform a left outer join using `Table.AddJoinColumn`, you need to specify the `joinKind` argument as `2`. For example:

Right Outer Join

A right outer join returns all the rows from the right table (`table2`), and the matching rows from the left table (`table1`). If there are no matching rows in the left table, the result will contain null values for the columns from the left table. To perform a right outer join using `Table.AddJoinColumn`, you need to specify the `joinKind` argument as `3`. For example:

Full Outer Join

A full outer join returns all the rows from both tables, and null values for the columns that don’t have a match in the other table. To perform a full outer join using `Table.AddJoinColumn`, you need to specify the `joinKind` argument as `4`. For example:

Performance Considerations

When working with large data sets, performance can become an issue. `Table.AddJoinColumn` has several optional parameters that can help to improve performance:

– `joinAlgorithm`: This parameter specifies the algorithm to use for joining the tables. The default value is `0`, which is the hash join algorithm. However, depending on the size and complexity of the data, other algorithms may be more efficient. For example, the sort merge join algorithm (`1`) can be faster for small or ordered data sets.

– `joinKind`: As we’ve seen, this parameter specifies the type of join to perform. Choosing the right type of join for your data can also have a significant impact on performance.

`Table.AddJoinColumn` is a powerful M function in Power Query that allows users to combine two tables based on a common column. With its support for different types of joins and performance optimizations, it can help to simplify complex data sets and make data transformation and cleaning processes more efficient.

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

Upcoming Courses

Contact Us