Understanding Join Types in Power Query

In this article we delve into the different join operations you can use to merge tables in Power Query. Table joins are used to combine rows between tables.

They compare tables based on one or more related columns. When the compared row values between two tables are identical they are considered a match.

There are joins that are meant to add values to a query, some return rows that differ from a table and others return only matching rows. No matter your requirement, mastering joins is a valuable skill for your ETL process.

And just like in SQL, Power Query supports the most common join types. So how does this work in Power Query?

How to Merge Queries

The easiest way join tables is by using merge queries in Power Query. To start, go to the Home tab in the ribbon and navigate to the Combine section. There you will find the Merge Queries button. You have two options there:

Merge Queries in Power Query

Merge Queries performs a join on your current table and adds the result of this merge in the same query. This means you can easily see the impact of the merge in a single place.

Merge Queries as New performs the same join on your table but puts the result of the merge in a separate query. The benefit of this approach is that your starting query remains the same. This means that you can reference that starting query for other purposes.

After choosing your desired way to merge queries, the below pop-up appears.

Merge Queries Screen

The default join types use terms like Left Outer, Right Outer, Left Anti etcetera. In Power Query this can be confusing in the beginning.

After all the tables show in the top and bottom of the screen, not so much on the left and right. It’s good to realize that Power Query considers the Top Table as the Left Table and the Bottom table as the Right Table.

For the rest of this article, we will consider the Left Table ‘Table1‘ and the right table ‘Table2’. This will help you understanding the code for each join.

You can easily follow along with the examples by copying these two tables into Power Query:

// Save below table as query with name "Table1"
= Table.FromRecords( { [Key = "B", T1 = "#"], [Key = "C", T1 = "@"],
                       [Key = "D", T1 = "="], [Key = "E", T1 = "$"] }, 
    type table[Key = Text.Type, T1 = Text.Type] 
   )

// Save below table as query with name "Table2"
= Table.FromRecords( { [Key = "A", T2 = 10], [Key = "B", T2 = 15],
                       [Key = "C", T2 = 20], [Key = "F", T2 = 60] }, 
    type table[Key = Text.Type, T2 = Int64.Type] 
   )

So which join types are available and what code do we use for them?

Join Types

Power Query has 6 join types available in the Merge Queries interface. We will now discuss them and see which code Power Query generates to perform the joins.

The default merge operation in Power Query makes use of Table.NestedJoin function. The syntax for it is:

= Table.NestedJoin(
    table1                        as table,     // Left Table
    key1                          as any,       // Column(s) to match
    table2                        as any,       // Right Table
    key2                          as any,       // Column(s) to match
    newColumnName                 as text,      // Column with joined rows
    optional joinKind             as nullable number, 
    optional keyEqualityComparers as nullable list
)

What differentiates one join from the other is the joinkind type you can input in your formula. JoinKind indicates to Power Query what merge operation to perform.

You can write out the full name for the Join Types or use the shorthand value for it:

Join Type Parameter Value
Inner Join JoinKind.Inner 0
Left Outer Join JoinKind.LeftOuter 1
Right Outer Join JoinKind.RightOuter 2
Full Outer JoinKind.Fullouter 3
Left Anti JoinKind.LeftAnti 4
Right Anti JoinKind.RightAnti 5

Performing a join operation with Table.NestedJoin adds a column with table objects to the Left Table. Each table object contains the joined rows from the Right Table. The Table.ExpandTableColumn function then allows you to expand the table.

Let’s focus on the different join types and what they do.

Left Outer Join

One of the most common joins is the Left Outer Join. The left outer join returns all rows from the Left Table (table1). Then by matching one or more column values with rows from table1 with similar column(s) from rows in table2, the join returns the matching values of the Right Table (table2).

For rows that don’t match with any row of table2, the join operation returns null. In the below picture the null values are represented by the empty white cell.

This join is very similar to the VLOOKUP or XLOOKUP formula in Excel. You often see it used when combining information from different tables that have a unique key to match on.

Left Outer Join in Power Query M

To perform a Left Outer Join from Table1 with Table2 based on the column ‘Key‘, Power Query generates the following code when using Merge Queries as New:

= Table.NestedJoin(
    Table1,            // Left Table Name
    {"Key"},           // Left Table Join Column 
    Table2,            // Right Table Name
    {"Key"},           // Right Table Join Column 
    "Table2",          // Name of Column with joined Rows
    JoinKind.LeftOuter // Join type
)

Right Outer Join

The Right Outer Join returns all values from the Right Table (Table2) while only returning the matching values from the Left Table (Table1).

Its behavior is very similar to the Left Outer join, except that it works the other way around.

Right Outer Join in Power Query M
= Table.NestedJoin(
    Table1,   {"Key"},            
    Table2,   {"Key"},         
    "Table2", JoinKind.RightOuter // Join type
)

Full Outer Join

There are cases where you want to return all rows from both Table1 and Table2. In those cases the Full Outer Join is your friend.

The Full Outer Join tries to match column values from Table1 with Table2 and if successful puts them next to each other.

The values of Table1 that don’t exist in Table 2 get their own row. Any rows without a match in Table 2 return null values for these rows.

Similarly the values of Table2 that don’t exist in Table1 also get their own row. For these rows the missing columns from Table1 get null values.

Full outer Join in Power Query M

To perform a Full Outer Join you can use this formula:

= Table.NestedJoin(
    Table1,   {"Key"},            
    Table2,   {"Key"},         
    "Table2", JoinKind.FullOuter // Join type
)

Left Anti Join

The Left Anti Join only returns rows from the Left Table (Table1). Based on the key columns, the join searches which of the values in Table1 do not exist in Table2. It then only returns the values unique to Table1.

Left Anti Join in Power Query M
= Table.NestedJoin(
    Table1,   {"Key"},            
    Table2,   {"Key"},         
    "Table2", JoinKind.LeftAnti // Join type
)

The Left Anti Join is a great way to perform checks on your queries. Imagine having a dimension table with stores and a fact table with store transactions. The Left Anti Join easily checks whether all StoreID’s in your fact table are available in your dimension table.

Right Anti Join

The Right Anti Join is the twin sister of the Left Anti Join. It returns the rows from the Right Table (Table2) which are not present in the Left Table (Table1).

Right Anti Join in Power Query M
= Table.NestedJoin(
    Table1,   {"Key"},            
    Table2,   {"Key"},         
    "Table2", JoinKind.RightAnti // Join type
)

You could say you can achieve the exact same result with the Left Anti Join by swapping around the Left Table and the Right Table in your join.

The choice which of the two joins to perform depends on where in your queries you want to see the result. If you quickly want to compare which values of another table are not present in the current table, the Right Anti Join lets you do it right away.

Yet if you want to see which values of the current table miss in another, the Left Anti Join lets you hit the ground running.

Inner Join

The Inner Join is a useful join that returns only those rows that having matching values in both the Left Table and the Right Table.

This can be useful when creating tables for your data model. Imagine filtering down a transaction table to contain only those transactions your want to analyze, but your product table still contains all products from your database.

You can easily perform an inner join between the transaction table and the product table so you only work with relevant rows.

Inner Join in Power Query M
= Table.NestedJoin(
    Table1,   {"Key"},            
    Table2,   {"Key"},         
    "Table2", JoinKind.Inner // Join type
)

So far we have looked at the default join types in Power Query. However, there may be cases where you want to perform ‘special’ type of joins.

Special Join Types

There are some special joins in Power Query that are not available by default. However, with some manual code you can perform them with ease.

Cross Join

The Cross Join is a join that takes two tables and returns all possible combinations of the table rows. This can result in an enormous table.

The result of this join is always the number of rows of Table1 multiplied by the number of rows in Table2. If both tables have 100 rows the join returns a table of 100 x 100 = 10.000 rows.

Cross Join in Power Query M

You can perform a Cross Join in multiple ways.

Method 1: Reference Table in Custom Column

The easiest way is to go to the query that contains Table1 and add a Custom Column. Name this column Crossjoin_Table2, and in the Formula Box write the name of the Table you want to Cross Join.

In this case write Table2 and click okay. This results in the below setup.

Cross Join in Power Query

You now created an additional column with several table objects. All that’s left is to press the two arrows in the right top of the Cross Join column, and press expand. You now have your Cross Joined table.

The downside of this Method is that it’s relatively slow. Which is why there is method 2.

Method 2: Left Join on Column with Single Value

A faster method, is to add a Custom Column to both Table1 and Table2 with a dummy value. For example the number 1.

After adding this column to both tables, perform a Left Outer Join from Table1 to Table2 and match the tables by your newly created Custom Column. Each row in Table1 will match each row in Table 2 and therefore return all rows for each cell.

Although the result is the same, method 2 is a much quicker one that I recommend. Just make sure to delete the unnecessary columns with dummy values after performing the cross join.

Full Anti Join

In cases where you want to inspect the differences between two tables, the Full Anti Join can come to the rescue. The Full Anti Join returns values from both the Left Table and the Right Table. That means rows from Table1 that don’t exist in Table2, and rows from Table2 that don’t exist in Table1, all based on a comparison key.

Full Anti Join in Power Query M

There is no direct method available in Power Query to achieve the Full Anti Join. With four easy ways you can achieve the same outcome.

The first way is to perform a Left Anti Join and a Right Anti Join separately and combine the two tables in the step after.

To make your life easier you can perform two Left Anti Joins, but in the second version swap around Table1 by Table2. This makes sure the results return the same setup, without the need to expand columns. That means:

let
  LeftAntiT1 = Table.NestedJoin(
                  Table1,    {"Key"},          
                  Table2,    {"Key"},       
                  "Table2",  JoinKind.LeftAnti // Join type
               ),
  LeftAntiT2 = Table.NestedJoin(
                  Table2,    {"Key"},          
                  Table1,    {"Key"},       
                  "Table2",  JoinKind.LeftAnti // Join type
               ),
  CombinedTable =     Table.Combine( { LeftAntiT1 , LeftAntiT2 } ),
  RemoveMergeColumn = Table.RemoveColumns( CombinedTable, {"Table2"} )
in
   RemoveMergeColumn

Alternatively, you can perform a Full outer Join, keep the rows with null values for T1 or T2 and combine the key columns:

let
  FullOuterJoin =           // Perform a full outer join
    Table.NestedJoin(
        Table1,    {"Key"}, 
        Table2,    {"Key"}, 
        "Table2", JoinKind.FullOuter
    ), 
  ExpandColumns =           // Retrieve all values from both tables
     Table.ExpandTableColumn(
         FullOuterJoin, "Table2", 
         {"Key", "T2"}, {"Key.1", "T2"}
     ), 
  T1orT2isNull =            // Keep rows where T1 or T2 = null
     Table.SelectRows( ExpandColumns, each [T1] = null or [T2] = null), 
  MergeKeys = 
    Table.CombineColumns(   // Combine Key Column values
        T1orT2isNull, 
        {"Key.1", "Key"}, 
        Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
        "Key"
    )
in
  MergeKeys

As a third method, you can first append/combine the two tables. Then group your data based on the comparison columns.

Just make sure to use an All Rows aggregation and a Count of the summarized rows. Filter your dataset only on unique rows and you will find which rows are unique to each table.

let
  CombinedTables = Table.Combine( { Table1, Table2 } ), 
  GroupedRows = 
    Table.Group(
        CombinedTables, 
        {"Key"},     // your comparison join column(s)
        {
          {"Count", each Table.RowCount(_), Int64.Type}, 
          {"Details", each _, type table 
              [Key = nullable text, T1 = nullable text, T2 = nullable text]}
        }
      ), 
  #"Select UniqueRows" = Table.SelectRows(GroupedRows, each [Count] = 1), 
  KeepDetailsColumn = Table.SelectColumns(#"Select UniqueRows", {"Details"}), 
  ExpandColumns = 
    Table.ExpandTableColumn(
        KeepDetailsColumn, 
        "Details", 
        {"Key", "T1", "T2"}, 
        {"Key", "T1", "T2"}
    )
in
  ExpandColumns

And lastly you can make use of the Table.RemoveMatchingRows function.

Table.RemoveMatchingRows( 
      Table1, 
      Table.ToRecords(Table2[[Key]]), {"Key"}
   )
  & Table.RemoveMatchingRows( 
      Table2, 
      Table.ToRecords(Table1[[Key]]), {"Key"}
   )

Self Join

A self join is a join like all the others, but the table is joined with itself. In the Merge Queries interface you can select the table to merge. When you want to do a self-join, you can simply fill in the same table twice.

Below pictures shows how Table1 is shown is Left Table (in the top) and as Right Table (in in the bottom. The same query will always have the suffix “(Current)” at the end of the table name.

Self Join in Power Query M

Performing a Self Join with the Left Outer Join on two index columns, as in above example, results in a table that shows the values of the previous row as result.

Union or Append

What can’t miss when working with tables is the option to Combine tables. You can Union tables in Power Query using the Append Queries option.

Append Queries in Power Query

The Append screen allows you to combine 2 or more tables through the user-interface.

Append Queries Screen

The Append Queries functionality combines two or more tables by attaching them on top of each other. When combining the tables Power Query puts columns with identical names in the same column. It compares the names in a case sensitive way.

When the Left Table (Table1) has a column that does not exist in the Right Table (Table2), the combined table will show the column regardless. The rows that belong to the table that misses this column will then show null values.

Append or Union in Power Query M

Appending Table1 with Table2 then happens through:

= Table.Combine( { Table1, Table2 } )  // Combines Table1 with Table 2

= Table1 & Table2                      // Short version to combine tables

= Table.Combine( { Table1, Table2, Table3 } ) // Combine three tables


Join Types Cheat Sheet

To put all of this together in an easy to digest page, I created below cheat sheet.

Join Types Cheat Sheet

Want the cheat sheet in full size? You can download it for free on this page.

Summary

This article covered the different type join types in Power Query. Additionally, you also learned how to perform some special joins, unavailable to the user-interface.

Which method is your favorite and how has it helped you? Let me know in the comments.

That was all for this post, enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<
 List Functions (200+ examples)
 Text functions (150+ examples)
Creating Tables from Scratch (40+ examples)
 Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

2 thoughts on “Understanding Join Types in Power Query”

  1. It is a perfect article, thank you so much Rick. I am sure everyone who need to know about the joins, should read your blog.

    Reply
    • Thank you Adem, really happy to hear that. I hope the cheat sheet finds its way to a lot of people, so they can have it as a reference.

      Reply

Leave a comment