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.
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)
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:
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:(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:or
In PowerBI Desktop, there's a dropdown to choose the join kind.