Table.Join

D

Back to top

T

Back to top

#

Back to top

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

What is Table.Join?

Table.Join is a Power Query M function that allows you to join two or more tables based on a common column. The function takes two or more tables as input, and returns a new table that combines the columns from each input table. You can specify the type of join to use (left, right, inner, or full outer), as well as the columns to include in the output table.

The M Code Behind Table.Join

To use the Table.Join function in Power Query, you need to understand the M code behind the function. The M code is a functional programming language used to create queries in Power Query. Here’s an example of the M code for a simple Table.Join function:

let

Source1 = Table.FromRows({{"John", 25}, {"Jane", 30}}, {"Name", "Age"}),

Source2 = Table.FromRows({{"John", "New York"}, {"Jane", "Los Angeles"}}, {"Name", "City"}),

JoinTable = Table.Join(Source1, "Name", Source2, "Name")

in

JoinTable

In this example, we have two tables: Source1 and Source2. Source1 contains two columns: Name and Age, while Source2 contains two columns: Name and City. We want to join these tables based on the common column “Name”. The M code for the Table.Join function is:

Table.Join(Source1, "Name", Source2, "Name")

This code tells Power Query to join the tables Source1 and Source2 on the column “Name”. The output table will contain columns from both input tables, with matching rows joined together.

Types of Joins

Table.Join allows you to specify the type of join to use when combining tables. There are four types of joins you can use:

Left Join

A left join returns all the rows from the left table, and the matching rows from the right table. If there is no match in the right table, the resulting columns will contain null values. Here’s an example of a left join:

Table.Join(Source1, "Name", Source2, "Name", JoinKind.LeftOuter)

Right Join

A right join returns all the rows from the right table, and the matching rows from the left table. If there is no match in the left table, the resulting columns will contain null values. Here’s an example of a right join:

Table.Join(Source1, "Name", Source2, "Name", JoinKind.RightOuter)

Inner Join

An inner join returns only the matching rows from both tables. Here’s an example of an inner join:

Table.Join(Source1, "Name", Source2, "Name", JoinKind.Inner)

Full Outer Join

A full outer join returns all the rows from both tables, with null values in the resulting columns if there is no match. Here’s an example of a full outer join:

Table.Join(Source1, "Name", Source2, "Name", JoinKind.FullOuter)

Customizing the Output Table

Table.Join allows you to customize the output table by specifying which columns to include. You can use the SelectColumns function to select specific columns from each table, or you can use the RenameColumns function to rename columns in the output table. Here’s an example of how to customize the output table:

let

Source1 = Table.FromRows({{"John", 25}, {"Jane", 30}}, {"Name", "Age"}),

Source2 = Table.FromRows({{"John", "New York"}, {"Jane", "Los Angeles"}}, {"Name", "City"}),

JoinTable = Table.Join(Source1, "Name", Source2, "Name"),

SelectColumns = Table.SelectColumns(JoinTable, {"Name", "Age", "City"}),

RenameColumns = Table.RenameColumns(SelectColumns, {{"City", "Location"}})

in

RenameColumns

In this example, we’ve selected the columns “Name”, “Age”, and “City” from the output table, and then renamed the column “City” to “Location”. The resulting table will contain only the selected columns, with the column “City” renamed to “Location”.

Table.Join is a powerful function in Power Query that allows you to combine data from multiple sources into a single table. By understanding the M code behind the function, you can customize the output table and use different types of joins to meet your specific needs. With Table.Join, you can easily clean and transform your data, and prepare it for analysis in Excel or Power BI.

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)