Print

Joining tables in Power BI with Power Query and DAX

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.

Table of contents

1. Type of joins
2. Joins in Power Query
3. Join tables in DAX

Types of Joins/ Merge options available

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:

Explain the different types of join/merge available in Power BI/ Excel

Structured Learning

If you want to learn more about Power Query or M, these guided courses might be perfect for you:

Type of joins and examples

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.

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 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? 🙂

Joins Cheat Sheet – Free download

I have actually created a guide to remember all this joins:

Joining tables in Power BI and Power Query

and you can download it here (Get download #31 from the community downloads folder).

Practice 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

Practice joining tables in 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:

  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

Here is an overview of the joins with Dax functions:

join tables with dax

With generate, you can do an inner join and outer join of tables using DAX:

GENERATE, GENERATEALL:

To download the files from our download center click *HERE*.

DAX Fridays! #64: GENERATE, GENERATEALL

NATURALINNERJOIN, NATURALLEFTOUTERJOIN:

04:35 Innerjoin
07:59 Outer join

DAX Fridays! #62: NATURALINNERJOIN and NATURALLEFTOUTERJOIN

More tutorials on Joining tables: CROSSJOIN

DAX Fridays! #61: CROSSJOIN

In this video I will show you how to do an anti join of tables using DAX:

EXCEPT:

DAX Fridays! #65: EXCEPT

Download example files

Was this helpful?

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *

Table of Contents