I have done a few videos on YouTube explaining how to join tables using Power Query or DAX. If you follow the channel, you probably have seen the videos and this blog post will serve as a compilation of all the material.
However, if you are new, this will serve as a tutorial for beginners on how to joins in Power BI. Either way, I believe this post will be useful for all, so lets get started.
Joining tables in Power BI
If you want to join tables using power query there are a few options:
- Left Outer
- Right Outer
- Full Outer
- Inner
- Left Anti
- Right Anti
It doesn’t say much right? What are all those joins?
Before jumping into how to do joins in Power BI, it is worth to take a few minutes to explain what those joins are with an example.
If you prefer a video, watch the video below otherwise continue reading:
An example on how to join tables
Let’s imagine that we have a bike company and we have in our data warehouse two tables: One with a list of products and price and another one with a list of products that we have in stock. We have in stock more products than those we manufactured ourselves and this list contains the entire list of products in stock. Here is how the tables look like:
The green rows represent the rows that match on both tables.
Let’s join those tables using the different combinations of joins available in Power BI:
Left Outer join
Let’s say that somebody in manufacturing wants to know which bikes we have in stock. In that case we would do a left outer.
When we do a Left Outer, we are taking all the rows from A and the matching ones from table B. Left outer will return from table B only the products that are present in table A.
Right Outer Join
Now, somebody working at the warehouse wants to know which products we manufacture ourselves. In that case we would do a right outer.
When we do a Right Outer, we are taking all the rows from table B and the matching ones from table A. Right outer will return from table A only the products that are present in table B.
Full outer Join
Product management department asked you for a list of all products available for sale. In this case, you will do a Full Outer.
When we do a Full Outer, we are taking all the rows from table A and all rows from table B. Full outer will return a table with all records, matching the ones that are available on both tables.
Inner Join
The planning department asked you for a list of products that are in stock. They don’t want to see any other products as they are not supposed to be in stock. In this case, you will do a Inner join.
When we do a Inner join, we are taking only the matching rows from table A and table B. Inner join will return a table with all matching records, excluding everything else.
Left Anti Join
Product management called you again, this time they want a list of products that are not in stock to review their strategy. No problem, in this case Left Anti is all you need.
When we do a Left Anti, we are taking all the rows from A that do not have a match in table B. Left anti will return all rows from table A that do not have a match on table B.
Right Anti Join
The logistics department want a list of products that are in stock but we don’t manufacture ourselves.. This time, Right Anti will do it.
When we do a Right Anti, we are taking all the rows from B that do not have a match in table A. Right anti will return all rows from table B that do not have a match on table A.
Easy peasy, right? 🙂
I have actually created a guide to remember all this joins:
and you can download it here.
Joining tables in Power Query
So now that you know what the different joins are, lets see how to do them in Power Query in this video:
Keynotes:
Left Outer Join 01:37
Right Outer Join 02:51
Full Outer Join 03:20
Inner Join 03:39
Left Anti Join 03:52
Right Anti Join 04:17
Join tables with DAX
Ok, so now that we are experts on joins, let’s try to join tables using DAX.
You can do the same types of joins in DAX as you do in Power BI. There are many ways to do it, and I will show you now some examples of DAX functions that will allow you to join tables.
I dont have just one video for this, but one video per function as this is part of my DAX Fridays series, but I will put a link here so you have access to all of them in one place.
But first of all, what function to use for what? Here it is:
- Left Outer: GENERATEALL, NATURALLEFTOUTERJOIN
- Right Outer: GENERATEALL, NATURALLEFTOUTERJOIN
- Full Outer: CROSSJOIN, GENERATE, GENERATEALL
- Inner: GENERATE, NATURALINNERJOIN
- Left Anti: EXCEPT
- Right Anti: EXCEPT
I have created the same visual but with DAX functions:
and it is included in the same guide, here.
Tutorial with example
With generate, you can do an inner join and outer join of tables using DAX:
GENERATE, GENERATEALL:
NATURALINNERJOIN, NATURALLEFTOUTERJOIN:
04:35 Innerjoin
07:59 Outer join
More tutorials on Joining tables: CROSSJOIN
In this video I will show you how to do an anti join of tables using DAX:
EXCEPT:
Download example file:
Link to sample pbix here
Let us know your thoughts...