Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 let's get started.
If you want to join tables using power query there are a few options:
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:
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.
This is what 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:
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.
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.
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.
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 an Inner join.
When we do an 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.
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.
The logistics department wants 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 created a guide to remember all these joins:
and you can download it here.
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 don't 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, what function to use for what?
Here it is:
I have created the same visual but with DAX functions:
and it is included in the same guide, here.
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
Link to sample pbix file here.
Hope you find this article useful for your future joins 😉
/Ruth
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.