Forum Discussion
Full outer join
Hello all.
I would like to use the picture from Microsoft as an example.
After combine the left and right table, it becomes the merged table below.
How can I get the last row CountryID show as 4?
I want to keep all ID from both tables rather than null.
And I would like to group few more columns together like ID column. Thanks.
7 Replies
When you do a merge in Power Query you get the option to choose your join kind, here you can choose Full Outer.
Thanks but full outer can only show as 1st picture but not 2nd picture.
Yes, you can do it in power query For it
watch my video here
https://1drv.ms/v/s!AiUZ0Ws7G26RhkcKM6nHiGi6ziQt?e=J299WE
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhkY9ZiZGmrFfbq7O?e=KLRbbsThanks for your video! I found my example is not enough for what I want.
If ID in Table 1 consists 1, 2, 3, 5
and ID in Table 2 consists 1, 2, 4, 6
and the ID in joined table shows all of the ID in the same column, which are 1, 2, 3, 4, 5, 6?
Thanks but full outer can only show as 1st picture but not 2nd picture.
A late reply.
I also can not understand the behaviour of "Outer join", which is described as "keep all rows from both tables".
One fail-proof solution would be to start with creation of a comprehensive list of all (distinct) ID's and only then merge the two tables one by another.
Of course it can be further developed to a smarter and more dynamic solution (like merge more tables at once and expand all columns), but the basic is this:let Source = Table.FromList( List.Distinct( List.Combine({Table1[id1], Table2[ID2]} ) ) ,Splitter.SplitByNothing()), #"Renamed to ID" = Table.RenameColumns(Source,{{"Column1", "all_IDs"}}), #"Merged Table1" = Table.NestedJoin(#"Renamed to ID", {"all_IDs"}, Table1, {"id1"}, "Table1", JoinKind.LeftOuter), #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Table1", "Table1", {"val1"}, {"val1"}), #"Merged Table2" = Table.NestedJoin(#"Expanded Table1", {"all_IDs"}, Table2, {"ID2"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Table2", "Table2", {"val2"}, {"val2"}) in #"Expanded Table2"
