Power BI: Merge – Join Kind
BY: David Rohlfs
Introduction
There are a lot of little aspects that can quickly entangle you with questions and difficulties while building a report. Some of these are necessary for learning more about what is happening with your data, and some of these are just the nuances behind being a report builder. This blog is meant to go over some of the various types of Join Kind options for a merge. These are some of the more difficult aspects of report building, and not because they are hard to understand or use, but because they are hard to remember the difference between. This blog is meant to help you see what the differences are between the different kinds of merging and how each one can affect what data is put into your merge.
In this blog we will go over when to use each option for the Join Kind and what they are. Because we are only going through some different options you can choose in a much larger function, we will not be covering some of the common problems (because most will be semantic model specific), and we will not do a walkthrough.
If you want to learn more about merging and what it entails, I suggest you go and read EPM Strategy’s blog on Merge and Append linked below. If you would like to learn about how you can do a merge using DAX, EPM Strategy’s blog on the Join DAX Functions will also be linked below.
When to use it
To start, I want to show you what the current options for Join Kind are. These are in the dropdown box “Join Kind” while you are using a merge function.
The goal for each of the sections below is only to give you a basic understanding of each Join Kind. Because there are so many, and there is a very simple sample semantic model, a lot of the nuances and complexities with each option will not be fully explored in this blog. To get a better understanding of each of these, the best thing you can do is to try them yourself and find what you can do to better explore your abilities.
Note: If you do not yet understand that a merge needs a row specific column to match between, then I suggest reading through EPM strategy’s blog on either Merge and Append or Row Specific Columns (Links Below).
There are two main types of Join Kinds inside of the merge function. There are the ordinary joins (i.e., Left Outer, Right Outer, Full Outer, Inner), and there are the Anti Joins (i.e., Left Anti, Right Anti). Both are useful in different ways and they both achieve a merge. It is important to learn the basics between the two types because it will greatly help you recognize what style of merge you want to choose when you are implementing a merge.
The four ordinary joins below are likely what you will be choosing when you do a merge. They are the most used and the simpler to understand.
Left Outer Join:
This join takes all the rows from the first column, and only gives you the rows from the second column that have matching IDs to the First. There are a couple of examples below that will show you what happens if the first table has the same number of rows as the second table, if the first table has more rows than the second table, if the second table has more rows than the first table, and if there are blank cells present.
Right Outer:
This join takes all the rows from the second table and only uses the rows from the first table that have matching IDs. This is like the Left Outer Join, except opposite syntax. So, you could achieve the same result just by switching table one with table two and vice versa. There are a couple of examples below that will show you what happens if the first table has the same number of rows as the second table, if the first table has more rows than the second table, if the second table has more rows than the first table, and if there are blank cells present.
Full Outer:
This join will take all the rows from both tables and match them up by their IDs. This Join Kind is a lot more prone to having blank cells and can be difficult to manage depending on the semantic model that you are using. There are a couple of examples below that will show you what happens if the first table has the same number of rows as the second table, if the first table has more rows than the second table, if the second table has more rows than the first table, and if there are blank cells present.
Inner:
This join is only taking rows that match from both table one and table two. So, if you have rows in either table that don’t match the other, they will be discarded in this merge. This merge is the least likely to leave blank cells out of the four ordinary joins, but the most likely to delete wanted data. There are a couple of examples below that will show you what happens if the first table has the same number of rows as the second table, if the first table has more rows than the second table, if the second table has more rows than the first table, and if there are blank cells present.
Now we get to go to the Anti joins. These joins are not as often used as the ordinary joins above, but they are especially handy when you want to create reports that emphasize tables that don’t have matches. For example, a report showing products that are no longer available, or a report that shows exceptions to normal scenarios in the data.
Left Anti:
This join kind is going to take the rows from the first table that do not have a match in the second table. Think of this as being the opposite of the Left Outer join. Instead of taking the rows that only match, you are only taking the rows that don’t match. There is an example below that will show you how this Join Kind works.
Right Anti:
This join kind is doing the same thing as the Left Anti except for on the right table. The goal for this join kind is that you are taking all the rows from the second table that don’t have matching rows to the first table. Think of this as being the opposite of the Right Outer join. There is an example below that will show you how this Join Kind works.
Conclusion
I hope that this blog helps to clarify what each join kind is and how it reacts to basic data. Remember that we were only touching the tip of the iceberg with the sample semantic model in this blog. There are a lot of nuances to each of these functions that you should take time to learn if you are wanting to expand your knowledge in merging semantic models. I hope you enjoyed this blog, and please check out some of the related EPM Strategy blogs below.
Links:
Comments
Leave a Reply
You must be logged in to post a comment.