Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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 6 regular join types and 3 special ones. Plus, you’ll learn a method to combine tables together. You’ll 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

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 behaviour that follows depends on the join type.

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 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 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. This menu allows you to choose the Merge type you want to perform.

Merge Queries Screen

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 TypeParameterValue
Inner JoinJoinKind.Inner0
Left Outer JoinJoinKind.LeftOuter1
Right Outer JoinJoinKind.RightOuter2
Full OuterJoinKind.Fullouter3
Left AntiJoinKind.LeftAnti4
Right AntiJoinKind.RightAnti5

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

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. One difference is that when Power Query encounters multiple matches, this 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.

Left Outer Join in Power Query M

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 behaviour 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:

Right Outer Join in Power Query M
= 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 by 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.

Full outer Join in Power Query M

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

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

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 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:

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 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:

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

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.

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

Cross Join in Power Query

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.

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.

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.

Self Join in Power Query M

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.

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 miss 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

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!

Join Types Cheat Sheet

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

Conclusion

In conclusion, I hope this article helped you understand the different ways you can join tables in Power Query. From regular joins like Inner, Left, Right and Full Outer, to special joins like Cross Join, Full Anti Join, and Self join, and also showing a way to Union them. You now have all the tools you need to bring different tables together in Power Query.

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. That was all for this post.

Enjoy Power Query!

Share on:
  1. Υoս actually make it seem so easy with your presentation bᥙt I find this matter to be really something tһat
    I think I would never understand. It seems too complicated and extremely Ƅroad
    for me. I’m looking forward for your next
    post, I will try to get the hang of it!

    Reply
  2. Would it be possible to create custom type of join in PQ ? Say, that you want to merge all records from one table with their overlapping records from the second table (start date/end date). Is it possible ?
    Thank you

    Matt

    Reply
    • Hi Matt. Yes that’s possible. It would involve some custom code that use Table.SelectRows and manually adds its filters.

      I hope to write a blogpost about that some day still !

      Reply
  3. Rick, thank you very much for such valuable information. It is very helpful to me. I do 99% of my work with Power Query. Greetings from Chile

    Reply
  4. 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.