Joins in Power Query: All 6 Types Explained

by

Power Query’s Merge compares two tables and returns matching or non-matching rows, depending on the join kind. Here are all six, explained with two cast lists, and the steps to use any of them.

WhereData → Get Data → Combine Queries → Merge
Join kinds6
Works inExcel 2016 and later, Power BI

The six joins at a glance

Venn diagrams of all six Power Query joins
Join kind Returns Example question
Inner Only rows in both tables Actors in both films
Full Outer All rows from both tables Everyone in either film
Left Outer All rows from the first, matches from the second Inception’s cast, with their other film if any
Right Outer All rows from the second, matches from the first The Dark Knight Rises cast, with matches
Left Anti Rows only in the first table Actors only in Inception
Right Anti Rows only in the second table Actors only in The Dark Knight Rises

The example tables

Two cast lists: Inception and The Dark Knight Rises. Some actors, like Michael Caine, appear in both.

Cast lists of Inception and The Dark Knight Rises in Excel
Actors who appear in both cast lists highlighted

Each join in pictures

Inner join Venn diagram
Full outer join Venn diagram
Left outer join Venn diagram
Right outer join Venn diagram
Left anti join Venn diagram
Right anti join Venn diagram

How to merge two tables

Step 1: Load both tables as connections

  1. Click in the first table and go to Data → From Table/Range.
  2. In the editor, choose Home → Close & Load → Close & Load To → Only Create Connection.
  3. Repeat for the second table.
Create Table dialog
Close and Load To menu
Import Data dialog with Only Create Connection

Step 2: Merge

  1. Go to Data → Get Data → Combine Queries → Merge.
  2. Pick the first and second tables and click the matching column in each (here, Actor).
  3. Choose the Join Kind and click OK.
Data, Get Data, Combine Queries, Merge
Merge dialog with tables, matching columns and join kind

Step 3: Tidy and load

The second table arrives as a column of nested tables. Click the expand icon in its header to pick columns, or remove it if you only need the names. Then Close & Load.

Things to know

  • Matching is case-sensitive and exact: “Tom Hardy ” with a trailing space won’t match. Use Transform → Format → Trim and Clean first.
  • You can match on two or more columns: hold Ctrl and click them in the same order in both tables.
  • Fuzzy matching (tick Use fuzzy matching) finds near matches like Bengaluru and Bangalore Urban. Check the results carefully.
  • Anti joins are the quickest way to find what’s missing between two lists, such as unpaid invoices.

Frequently asked questions

What is the difference between a left outer and a left anti join?

Left outer keeps every row from the first table and adds matches. Left anti keeps only first-table rows that have no match.

Related