Excel 2010 Power Query – How to Perform Outer Join with Union

microsoft excelpower-queryquerysql

I've got two tables (two worksheets of the same workbook in Excel 2010):

First table

Second table

The result should look like this:

enter image description here

My SQL code works well:

SELECT table_2.ID, table_1.value_1, table_2.value_2 FROM table_1 RIGHT JOIN table_2 ON table_1.ID = table_2.ID
SELECT table_1.ID, table_1.value_1, table_2.value_2 FROM table_1 LEFT JOIN table_2 ON table_1.ID = table_2.ID

Is there a way to do the same thing in Excel with Power Query?

Solved the problem using built-in Microsoft Query and SQL but still interested in a solution using Power Query.

Best Answer

So in Power Query there is an optional parameter for setting the join kind of a Merge Queries (i.e. a Table.NestedJoin function)

Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind as nullable number) as table

The default value is 1, which is a LEFT OUTER join.

And the default GUI for Merge Queries:

enter image description here

Generates a line in your PQL statement that looks like this:

Table.NestedJoin(#"Changed Type",{"ID"},Table1,{"ID"},"NewColumn")

Since the joinKind parameter isn't set at all, it defaults to a LEFT OUTER join.

If you do check the Only include matching rows checkbox, you'll perform an INNER join and the generated line looks like this:

= Table.NestedJoin(#"Changed Type",{"ID"},Table1,{"ID"},"NewColumn",JoinKind.Inner)

(NB: There's a JoinKind enum mapping to the magic numbers of the parameter: so JoinKind.Inner evaluates as 0, JoinKind.LeftOuter as 1, etc.)

In Excel, you have to modify this formula by hand to perform a FULL OUTER join:

= Table.NestedJoin(#"Changed Type",{"ID"},Table1,{"ID"},"NewColumn",JoinKind.FullOuter)


= Table.NestedJoin(#"Changed Type",{"ID"},Table1,{"ID"},"NewColumn", 3 )

In PowerBI Desktop, there's a dropdown to choose the join kind.

Related Question