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;
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.
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.
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.
Choose columns as below:
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:
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.
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.
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
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
Thank you, you did not please me :o) Do you know a source of information, where I could find this? An example would be great.
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
Hi Lea,
M definitely helps. Let me know if you have any questions.
Cheers,
Reza
Hi Reza ,
Can we add custom queries for Join in Power BI.If yes how can we do that.
Hi.
Do you mean custom SQL Queries? yes, you can paste your SQL Query in the Get Data from SQL Server, in advanced mode in the text box
Cheers
Reza