1

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

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:

enter image description here

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.