Power Query table merge

Question

Say I have two tables, both listing ID number and some value, for example,

Table 1

 ID  Value
 1   A
 1   B
 1   C

Table 2

 ID Value
 1  A
 1  D
 1  E

Using Power Query I would like to merge both tables in a way so if there is no matching value, the "missing" values are still listed, so the expected result of the above tables would be:

 ID Value 1 Value 2
 1  A       A
 1  B
 1  C
 1          D
 1          E

Edit: solved. I've created a "helper" column ID&Value in both tables and used FullOuter join when merging.

Answer 1

Try this:

let

//Read in the two tables
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Table 1" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Table 2" = Table.TransformColumnTypes(Source2,{{"ID", Int64.Type}, {"Value", type text}}),

//Join the column
    #"Join Column" = Table.NestedJoin(#"Table 1",{"ID","Value"},#"Table 2",{"ID","Value"},"Value 2",JoinKind.FullOuter),
    #"Expanded Value 2" = Table.ExpandTableColumn(#"Join Column", "Value 2", {"ID", "Value"}, {"ID 2", "Value 2"}),

    #"Replace null ID" = Table.ReplaceValue(
        #"Expanded Value 2",
        each [ID],
        each [ID 2],
        (x,y,z) as number=> if y = null then z else y,
        {"ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replace null ID",{"ID 2"}),

//Sort nulls to bottom
    #"Add Sort Column" = Table.AddColumn(#"Removed Columns","Sorter", each [Value]??"~~~"),
    #"Sorted Rows" = Table.Sort(#"Add Sort Column",{{"ID", Order.Ascending}, {"Sorter", Order.Ascending}, {"Value 2", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Sorter"})
in
    #"Removed Columns1"