Full outer join

[chloe914]

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.

[vhatp]

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"

[aditya0125]

use full outer join in query editor.

[chloe914]

Thanks but full outer can only show as 1st picture but not 2nd picture.

[Ahmedx]

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=KLRbbs

[chloe914]

Thanks 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? 

[Jamie-]

When you do a merge in Power Query you get the option to choose your join kind, here you can choose Full Outer.

[chloe914]

Thanks but full outer can only show as 1st picture but not 2nd picture.