Outer join with union in Excel 2010 using Power Query

Question

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

The result should look like this:

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
UNION
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.

Answer 1

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:

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

-- LEFT OUTER JOIN
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:

-- INNER JOIN
= 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)

or

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

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