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