SQL Joins/Merging in Power BI using Query Editor

SQL-Joins.jpg
There are several functions in M (the query editor language in Power BI) that can be used to merge/joins two tables of data.

In today’s blog post, we will explore how you can join different tables in Power BI Query Editor using Mashup language.

The syntax pattern for joining two tables is similar for all the joins except the fourth parameter which defines the join kind:
     Join= Table.NestedJoin(table1 as table, key1 as any,
                        table2 as any, key2 as any, 
                          newColumnName as text,
                             optional JoinKind.Type)

The M expression generated by the Merge dialog to join Table1 and Table2 together and only include matching rows, as shown in Figure 2, is as follows:
= Table.NestedJoin(Table1,{“Month”},
Table2,{“Month”},
“Table2”,
JoinKind.LeftOuter)
Left Join.jpg
Table.NestedJoin() returns a table with a single extra column containing values of type Table; the first four parameters define the two tables used in the merge and the columns on which to join. The final parameter allows you to specify what kind of join to
perform.

For Example,

JoinKind.Inner performs an inner join, where only the rows from both tables that match are returned.
JoinKind.LeftOuter performs a left outer join, where all rows from the table in the first parameter are returned but only matching rows from the table in the third parameter
are returned.
JoinKind.RightOuter performs a right outer join, where all rows from the table in the third parameter are returned but only matching rows from the table in the
first parameter are returned.
JoinKind.FullOuter performs a full outer join where all rows from both tables are returned.
JoinKind.LeftAnti performs a left anti join, where only the rows from the table in the first parameter that do not match any rows in the table in the third parameter are
returned.
JoinKind.RightAnti performs a right anti join, where only the rows from the table in the third parameter that do not match any rows in the table in the first parameter are
returned.

The Table.AddJoinColumn() function does exactly the same as Table.NestedJoin() function, only it does not allow you to specify the kind of join it does and only performs left outer joins.

= Table.AddJoinColumn(Table1,{“Month”},
Table2,{“Month”},
“Table2”)

Table.AddJoinColumn.jpg

 

The Table.Join() function is very similar to Table.NestedJoin() but instead of returning joined rows as a value of type Table, it returns a flattened table with the joined rows already expanded.

AddJoin.jpg

With the arsenal of these new weapons in Power BI, You can practice with the other join types described in this blog post and save your valuable time from not doing VLOOKUP anymore in Excel.

Happy Holidays and New Year from Business-Insights, Inc & Team…

Christmas.png