Forum Discussion

chloe914's avatar
chloe914
Frequent Visitor
3 years ago

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 ...
Need Help
vhatp's avatar
vhatp
Advocate I
1 year ago

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"