How to Change Joining Types in Power BI and Power Query

Posted by on Jul 31, 2015 in Power BI, Power Query | 6 Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

0

Joining tables is not a new concept, I bet all readers of my blog at least have a clue about that. However there are different types of joins, and applying these types of Joins are not all possible through Power Query GUI. Power BI recently took an step and implemented that in the GUI, however you might like to know how to apply that in the Power Query. The trick is that M is your friend, You can do whatever you want behind the scenes with M script.

I don’t want to go through the details of explaining every join type here. Picture below illustrated it perfectly;

joins

Picture referenced from: http://www.udel.edu/evelyn/SQL-Class2/SQLclass2_Join.html

Now let’s see how to use joins through Power BI and Power Query;

Power BI Desktop

In Power BI Desktop you can join two tables with Merge menu item in the Query Editor, in Home tab, Under Combine, Merge Queries.

1

The Merge Window will appear with ability to select first table (Left part of the join), and the second table (Right part of the join). You can choose columns that you want to participate as joining key within an order (you can choose multiple columns with Ctrl Key). And there is join kind that you can choose.

10

The default behavior is left outer join, which means all records from the first table, if there is any record in the first table that matches record(s) in the second table it would be listed as well.

After joining tables, the second table will appear as a field that has table value in it’s cells. What you need to do is to select columns that you want to show in the result set.

2

Choose columns as below:

3

 

You can also choose from one of other join types as mentioned below:

  • Left Outer (all from first, matching from second): this option was the default behavior previously within Merge dialog
  • Right Outer (all from second, matching from first)
  • Full Outer (all rows from both)
  • Inner (only matching rows); this option was available previously through “Choose only matching rows” option in Merge dialog
  • Left Anti (rows only in first)
  • Right Anti (rows only in second)

Power Query

At the time of writing this blog post Power Query Editor (GUI) only supports two types of joins mentioned above: Left Join, and Inner Join.

You should follow the same path through Merge Queries, and then you will see joining options as below:

As you see the default behavior is Left join. You can change it to inner join with selecting “Only include matching rows”.

Change Through M

You can apply any join type that you want simply by going to advanced editor, and changing the M script as below:

Go to View tab, and click on Advanced Editor:

4

In the Advanced Editor query window you can see the M script that builds the result set. Find the join function and change the JoinKind.

5

JoinKind is an enumeration type that can have below values:

  • JoinKind.Inner=0
  • JoinKind.LeftOuter=1
  • JoinKind.RightOuter=2
  • JoinKind.FullOuter=3
  • JoinKind.LeftAnti=4
  • JoinKind.RightAnti=5

So you can simply change it as you want.

 

This feature I reckon soon will be available on Power Query Editor GUI as well, but till that time the above description hopefully help you in any situation that you want to set a join type.

 

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

6 Comments

  • Hello Reza, this is nice, but is there a way how to perform some other joins? I mean joins with conditions such as LIKE, or BETWEEN? Thank you, Lea

    • Hi Lea,

      Thanks,
      Unfortunately Not-Equi joins (with conditions such as like, between….) are not supported in existing functions. It has to be written custom. Hopefully that comes at some stage as built in functions.

      Cheers,
      Reza

        • Hi Lea,

          I haven’t found any link that have implemented a not-equi join with Power Query. The point that I was [trying] to say is that: because it is not an out-of-the box feature or function, then it has to be written manually. At the moment unfortunately I am busy and don’t have time to develop such function. However I can guide you into the right direction;
          – like : for joining based on the like, you can filter the second table based on records that their joining key is like one of the records in the first table. then cross join the first record of the first table with every record of the second filtered table. then do it for the second record of the first table….. to the end.
          – between: for joining based on between, you can filter the second table based on records tha their joining key is BETWEEN the values mentioned in the first record of the first table, then cross join as mentioned above.
          These two methods are just something that comes into my mind right now, these are not definitely the best solution for this, but it should be workable. You could find other methods I believe as well.

          Cheers,
          Reza

          • Hi Reza,
            thank you very much for your answer. I will try your proposed method and keep on searching for some other solution. The “M” could help, maybe. It is interesting, that many BI tools only allow you to do (I mean in an easy way) the “=” join, and only one I have found so far allows you comfortably perform whatever join you need.
            Cheers,
            Lea

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">