Joins conceptually

New to this blog? Start the Intro to Power Query series here.

This is the 1st of 2 posts on joins and is a complement to the Merge post.

---

Let's assume you have two lists tracking journal entries on the first day of close: List A is entries booked and List B is entries expected. 

List A: Entries Booked Day 1	List B: Entries Expected Day 1
Cash	Cash
Rent	Utilities
Travel	Revenue

To merge these lists in Power Query, there are 6 types of joins available.

---

An inner join returns items if they are on both lists.  In this case, an inner join would return Cash.  Notice in the Venn diagram below that Cash is where both circles intersect.

An outer join in contrast will return all the items on the lists.  In this case, an outer join would return Cash, Rent, Travel, Utilities, and Revenue.  

---

A left outer join returns all the items on the first list.  Results would be Rent, Travel, and Cash.

A left anti join returns all the items on the first list that do not have a match on the second list.  Results would be Rent and Travel.

---

A right outer join returns all the items on the second list.  Results would be Utilities, Revenue, and Cash.

A right anti join returns all the items on the second list that do not have a match on the first list.  Results would be Utilities and Revenue.

---

The left outer join is closest to a VLOOKUP in native Excel.  Similar to a VLOOKUP, Merge is looking up items from a list, finding a match and returning the result. 

Contrary to a VLOOKUP, Merge returns each instance where there is a match, not just the first match it finds.

---

When using Merge in Power Query, the table on the top is the left side (List A) and the table on the bottom is the right side (List B).

---

The next blog post demonstrates the results of each type of join in Power Query and illustrates how duplication of records can occur. 

In this lesson, you learned about:

• the 6 different join types in Power Query; and,
• how to do a "VLOOKUP" across multiple columns using Merge.

Prior post: Remove duplicates
Next post: Joins illustration