All About Inner & Outer Joins with Table.Join (Power Query M Language)

Image by Editor
 

Joins are fundamental operations in data preparation, enabling you to combine data from multiple tables based on matching columns. Power Query provides Table.Join function that allows users to perform various joins easily. In this tutorial, we will explore Inner, Outer, and Anti joins with practical examples using Power Query M language.

What is Table.Join?

Table.Join is a function in Power Query M language that allows you to combine two tables based on matching values in specified columns. This is like SQL JOIN operations and is essential for data analysis and reporting.

Power Query supports several join types through the JoinKind parameter:

• Inner: Returns only matching rows from both tables.
• LeftOuter: Returns all rows from the left table, plus matching rows from the right table.
• RightOuter: Returns all rows from the right table, plus matching rows from the left table.
• FullOuter: Returns all rows from both tables.
• LeftAnti: Returns rows from the left table that don’t match any row from the right table.
• RightAnti: Returns rows from the right table that don’t match any row from the left table.

Syntax:

Table.Join( table1 as table, key1 as any, table2 as table, key2 as any,
joinKind as nullable text,
optional joinAlgorithm as nullable text,
optional keyEqualityComparers as nullable list
) as table

Where:

• table1: The left table in the join operation.
• key1: The column(s) from table1 used for matching.
• table2: The right table in the join operation.
• key2: The column(s) from table2 used for matching.
• joinKind: The type of join to perform.
• joinAlgorithm [Optional]: Parameter specifying the algorithm to use.
• keyEqualityComparers [Optional]: Optional list of functions for comparing keys.

To illustrate table joins, we’ll use two related datasets: Customer Data and Order Data. We loaded data from Excel to Power Query.

Customer Data:

Order Data:

Inner Joins: Combining Matching Rows

Inner join returns rows existing in both tables.

Power Query M Code:

let
	InnerJoinTable = Table.Join(
        #"Customer Data", {"Customer ID"},
    	#"Order Data", {"CustomerID"},
    	JoinKind.Inner
	)
in
	InnerJoinTable

With an inner join:

• Only customers with matching orders appear.
• Customers 103 and 104 are excluded because they have no orders.
• Order 5004 is excluded because Customer ID 105 doesn’t exist in the Customer table.
• John Smith appears twice because he has two orders.

Best used when: If you want only complete records where information exists in both tables.

Left Outer Joins: Keeping All Left Rows

Return all left table rows, matching right table rows if available.

Power Query M Code:

let
    LeftOuterTable = Table.Join(
        #"Customer Data", {"Customer ID"},
        #"Order Data", {"CustomerID"},
        JoinKind.LeftOuter
    )
in
    LeftOuterTable

With a left outer join:

• All customers appear in the results.
• Customers without orders (103 and 104) have null values for order fields.
• Order 5004 is still excluded because it doesn’t match any customers.

Best used when: If you want to include all records from your primary table regardless of whether they have matching records in the secondary table.

Right Outer Joins: Keeping All Right Rows

Returns all right table rows, matching left table rows if available.

Power Query M Code:

let
    RightOuterTable = Table.Join(
        #"Customer Data", {"Customer ID"},
        #"Order Data", {"CustomerID"},
        JoinKind.RightOuter
    )
in
    RightOuterTable

With a right outer join:

• All orders appear in the results.
• Order 5004 appears with null values for customer information.
• Customers without orders (103 and 104) are excluded.

Best used when: If you need to ensure all records from your secondary table are included, regardless of whether they have matching records in the primary table.

Full Outer Joins: Keeping All Rows from Both Tables

Returns all rows from both tables, merging matching rows.

Power Query M Code:

let
    FullOuterTable = Table.Join(
        #"Customer Data", {"Customer ID"},
        #"Order Data", {"CustomerID"},
        JoinKind.FullOuter
    )
in
    FullOuterTable

With a full outer join:

• All customers and all orders appear in the results.
• Customers without orders have null values for order information.
• Orders without matching customers have null values for customer information.

Best used when: if you need a complete view of all data from both tables, with no exclusions.

Anti Joins: Finding Records without Matches

Left Anti Join Example: Records only in Left Table

Returns rows from the left table without a match on the right table.

Power Query M Code:

let
    LeftAntiTable = Table.Join(
        #"Customer Data", {"Customer ID"},
        #"Order Data", {"CustomerID"},
        JoinKind.LeftAnti
    )
in
    LeftAntiTable

With a left anti join:

• Only customers without matching orders appear.
• It only includes columns from the left table.

Best used when: If you want to find records on the primary table that don’t have corresponding records in the secondary table (e.g., customers who haven’t placed orders).

Right Anti Join Example: Records only in Right Table

Returns rows from the right table without a match on the left table.

Power Query M Code:

let
    RightAntiTable = Table.Join(
        #"Customer Data", {"Customer ID"},
        #"Order Data", {"CustomerID"},
        JoinKind.RightAnti
    )
in
    RightAntiTable

With a right anti join:

• Only orders without matching customers appear.
• It only includes columns from the right table.

Best used when: if you want to find records in the secondary table that don’t have corresponding records in the primary table (e.g., orders from unknown customers).

Handling Duplicate Column Names Error

Inner joins can handle duplicate name errors, but outer joins flag errors and it is one of the most common errors when working with Table.Join.

CopyExpression Error: A join operation cannot result in a table with duplicate column names ["Customer ID"]

This occurs because both tables have a column with the same name. Power Query doesn’t allow duplicate column names in the result table.

Solution 1: Rename Columns Before Joining

An easy solution is renaming the column before joining either using code or you can do it manually in your dataset.

Power Query M Code:

let
   // Rename Customer ID in the Order Data table
   OrderDataRenamed = Table.RenameColumns(OrderData, {{"Customer ID", "Order Customer ID"}}),

JoinResult = Table.Join(
Customer Data,
"Customer ID",
OrderDataRenamed,
"Order Customer ID",
JoinKind.LeftOuter
)

in
    JoinResult

Solution 2: Use Table.NestedJoin

A more modern approach is to use Table.NestedJoin, which creates a nested table column that you can then expand.

Power Query M Code:

let

JoinedTable = Table.NestedJoin(
#"Customer Data", {"Customer ID"},
#"Order Data", {"Customer ID"},
"Order Details",
JoinKind.LeftOuter

),

ExpandedTable = Table.ExpandTableColumn(
JoinedTable, "Order Details", {"Order ID", "Order Date", "Product"}
)

in
ExpandedTable

This approach gives you more control over which columns to include and how to handle naming conflicts.

Solution 3: Use Table.PrefixColumns

Another option is to add prefixes to column names.

Power Query M Code:

let

// Add prefix to all columns in OrderData
OrderDataPrefixed = Table.PrefixColumns(OrderData, "Order_"),

JoinResult = Table.Join(
Customer Data,
"Customer ID",
OrderDataPrefixed,
"Order_Customer ID",
JoinKind.LeftOuter
)

in

JoinResult

The result will have columns like “Customer ID”, “Order_Customer ID”, “Order_Order ID”, etc.

Best Practices and Performance Tips

• Ensure matching columns have consistent data types.
• Select specific columns to avoid duplication.
• Clearly define join types to avoid unexpected results.
• Validate data after each join.

Conclusion

Table.Join in Power Query M language is powerful for merging tables using Inner, Outer, and Anti joins, which improves the data transformation process. It is a fundamental concept in data analysis, allowing you to connect related information and derive deeper insights. Mastering these joins ensures cleaner, more accurate data processing. Remember that different join types serve different analytical purposes. The key to success is understanding your data relationships and choosing the appropriate join type for your specific business question.