„Merge queries is one way to combine queries (or even different tables within the same query) in Power Query/M. This topic has already been extensively written by (among others) Jason Thomas, Reza Rad and Hans Peter Pfister and I can only recommend you to read these articles. I don’t intend to copy/repeat their contents in this article. So why write a new article on this topic?
Recently I stumbled across a very interesting project from the R-Community. This project created animated GIFs that animate the different join types, which in my opinion greatly improves the understanding of the join. Although this project is dedicated to learning join types in R, I took the liberty of borrowing this great output for learning in the Power BI community as well. Thanks to Garrick Aden-Buie for creating these great animated GIFs.

I am pretty sure this will help beginners to understand and trainers to train. Have fun.

Left Outer Join (all from first, matching from second)

Left Outer Join, when there is more than one match

In the community there are many comparisons of the Left Outer Join in Power Query with the VLOOKUP in Excel. Even if this idea may help the confirmed Excel user to find his way around, this comparison is still misleading. Why? Well, the VLOOKUP always searches for the FIRST match in the right table and therefore never changes the number of records in the result table, compared to the original left table. The Left Outer Join, on the other hand, does this if more than one match is found in the right table. The animated GIF illustrates this very impressively.

Right Outer Join (all from second, matching from first)

Even if there is no extra animated GIF for this, the Right Outer Join can of course have several hits in the left table and thus increase the number of records in the result table compared to the original right table.

 

Full Outer Join (all rows from both)

 

Inner Join (only matching rows)

 

Left Anti Join (rows only in first)

 

Right Anti Join (rows only in second)

In the shown project, there was unfortunately no equivalent to the right anti join and since I unfortunately have no knowledge about the R language, I could not create the corresponding animated GIF myself. However, I think that a look at the Left Anti Join should be enough to imagine how the Right Anti Join works.