Join Types in Power Query (+ Free Cheat Sheet)

In this article, you will learn about join types in Power Query. You can use them to combine queries or tables, but also to compare tables for differences.

We will delve into 8 regular join types and 3 special ones. Plus, you’ll learn a method to combine tables and even get to download a free cheat sheet that shows how each join type works. So, let’s get started and learn how to make joins work for you!

Table of contents

• What are Joins
• How to Merge Queries
• Join Types
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Left Anti Join
  • Right Anti Join
  • Inner Join
  • Left Semi Join
  • Right Semi Join
• Special Join Types
  • Cross Join
  • Full Anti Join
  • Self Join
• Union or Append
• Join Types Cheat Sheet
• Conclusion

What are Joins

Table joins can be used to compare or 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. The behavior that follows depends on the join type.

There are joins return only matching rows. No matter the requirement, mastering joins is a valuable skill for your data-cleaning 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

So, how do you perform a join in Power Query? The easiest way to join tables in Power Query is by using merge queries. 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 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. This menu allows you to choose the Merge type you want to perform.

The default join types use terms like Left Outer, Right Outer, Left Anti, etc. This setup can be somewhat confusing in the beginning.

After all, the above tables show at the top and bottom of the menu, 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 understand 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 join syntax can you use?

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 join queries.

The default merge operation in Power Query performs a Nested join by using the 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 new column with table objects to the (Left) table. Each table object contains the joined rows from the Merged (Right) Table. The Table.ExpandTableColumn function then allows you to expand the table.

Particular behaviour you should be aware of is the following. When Power Query encounters duplicates while performing a Merge, the number of rows in Table1 (Left Table) also duplicates with the same number. This means that by merging a table, the total number of rows in it can expand.

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).

The join operation returns null for rows that don’t match any row of Table2. 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. One difference is that when Power Query encounters multiple matches, it duplicates the rows with multiple matches, whereas VLOOKUP never duplicates rows.

You often see the Left Outer Join used when combining information from different tables that have a unique key to match.

To perform a Left Outer Join from Table1 with Table2 based on the column(s) in the ‘Key‘ argument, Power Query uses JoinKind.LeftOuter when using Merge Queries as New:

Table.NestedJoin(
  Table1,            // Left Table Name
  { "Key" },           // Left Table Join Column(s)
  Table2,            // Right Table Name
  { "Key" },           // Right Table Join Column(s)
  "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 its base table is the Right table instead of the Left. This also means that Right Outer Joins can duplicate rows when encountering multiple matches. It uses JoinKind.RightOuter:

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

Note that you can easily make a join based on multiple columns. To do that, you should click Merge Queries. Then, in the pane that opens, you can hold the CTRL key to select multiple columns for both the left and the right table.

Notice that when selecting multiple columns, an index number appears at the top of the column. The identical index numbers in Table1 and Table2 are compared during the join. The join performed will compare the two tables based on the combination of columns.

In case you want to perform a join based on two columns in the above situation, the code can show:

Table.NestedJoin(
  Table1,   { "Key", "T1" },            
  Table2,   { "Key", "T2" },         
  "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.

To perform a Full Outer Join, you can make use of the join type JoinKind.FullOuter:

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 for which values in Table1 do not exist in Table2. It then only returns the unique values from Table1.

You can instruct Power Query a Left Anti Join by using JoinKind.LeftAnti:

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 StoreIDs 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). To do that, it uses JoinKind.RightAnti:

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 of 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 have 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 you 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. Give instructions for the join by using JoinKind.Inner:

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 a ‘special’ type of join.

Left Semi Join

The left semi join is similar to the inner join. It returns all rows in the left table with matching rows in the right table. The main difference from an inner join is that this join does not allow you to return values from the right table.

Here’s the code you can use to perform the join:

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

This operation supports query folding with SQL based data sources and uses the SQL Exist clause. The main benefit of this join over the inner join is that the database can optimize it better and it may improve performance.

If you perform the above operation and expand the table object, it will return null values, as shown below:

You can see more details about this in our article about understanding Semi Joins in Power Query.

Right Semi Join

The right semi join checks which values from the right table have matches in the left table and returns only those matching rows from the right table. Like the left semi join, it doesn’t include any columns from the left table. Here’s an example of how that works:

You can use this code to perform a right semi join:

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

Suppose you apply the above expression to the table below:

When this expression is applied, the resulting table will have null values for columns from the left table, while a new column will contain a nested table with the matching rows from the right table.

Special Join Types

There are some special joins in Power Query that are not available by default. However, with some manual coding, 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 in 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.

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 OK. This results in the below setup.

You have 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 with 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.

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.

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

Performing a Self Join with the Left Outer Join on two index columns, as in the above example, results in a table that shows the values of the previous row as a 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.

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

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 miss this column will then show null values.

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

Are you looking for a handy guide to help you remember all the different join types in Power Query? Well, you’re in luck! In this section, you can download a free cheat sheet that shows you all the join types, including regular joins, special joins, as well as the Union method.

It’s like a little map that you can use to navigate through the different types of joins so you can pick the right one for your needs. I’ve also included a short description of how each join type works so you can understand the concepts behind it. So, get ready to download your cheat sheet and start joining tables like a pro!

Do you want the cheat sheet in full size? You can download the full-resolution files: click here for PDF or here for PNG.

Conclusion

This article has provided an overview of how to join tables in Power Query, covering standard joins like Inner, Left, Right, and Full Outer, as well as the newer Left and Right Semi Joins. We also explored special joins such as Cross Join, Full Anti Join, and Self Join, along with ways to union tables.

It’s important to remember that each join type has its use case and that you should choose the one that best fits your needs. With these tools, you can now efficiently combine data from multiple tables in Power Query.

Enjoy Power Query!