Joining tables In Power BI using Power Query and/or DAX

by ruthpozuelo on ‎02-06-2018 09:54 AM - last edited on ‎02-06-2018 09:57 AM by MindyJ Frequent Visitor

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.

 

Joining tables in Power BI

 

If you want to join tables using power query there are a few options:

  1. Left Outer
  2. Right Outer
  3. Full Outer
  4. Inner
  5. Left Anti
  6. 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 of 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.

 

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:

 

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.

 

left outer power bi excel power query

 

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 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.

 

 

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 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? Smiley Happy I have created a guide to remember all these 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 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:

  1. Left Outer: GENERATEALL, NATURALLEFTOUTERJOIN
  2. Right Outer: GENERATEALL, NATURALLEFTOUTERJOIN
  3. Full Outer: CROSSJOIN, GENERATE, GENERATEALL
  4. Inner: GENERATE, NATURALINNERJOIN
  5. Left Anti: EXCEPT
  6. Right Anti: EXCEPT

 

I have created the same visual but with DAX functions: join tables with dax

 

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 file here.

 

Hope you find this article useful for your future joins Smiley Wink

/Ruth

Comments
by Joerobert Regular Visitor
on ‎02-08-2018 04:51 AM

This looks like a great tutorial, i will dedicate some time today to review the material.

by anandav Established Member
on ‎02-08-2018 02:48 PM

A really good explanation of joint types and mapping that to DAX functions.

Makes life a lot easier when you have to remember what function to use.

by ruthpozuelo
on ‎02-13-2018 12:59 PM

Great, hope you find it useful Smiley Happy

/Ruth

by TedemanCPA Frequent Visitor
on ‎03-02-2018 05:32 PM

Wow, what an awesome & comprehensive post on table-joins in Power BI!  

by mannu Member
on ‎04-10-2018 06:00 AM

Is it possible to join a parameter list with values in one of the columns in a query (M query editor/ Advanced Editor).

I can create a table and do it. But interested to know if it can be accomplished via parameters in Power BI.

by anandav Established Member
‎05-11-2018 03:24 AM - edited ‎05-11-2018 03:26 AM

Hi Ruth,

 

Can I use the diagram with Power BI functions in my blog with due credit to you?

I am planning to document some details for my refrence but would like to use your diagram as it is very useful summary.

 Thanks,

Anand