Объединение таблиц Power Query

Объедините две таблицы с полной внешней таблицей, где значение является ключом, а затем очистите.

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Value"}, Table2, {"Value"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"ID", "Value"}, {"Table2.ID", "Table2.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "NewID", each if [ID] = null then [Table2.ID] else [ID]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"NewID", "ID", "Value", "Table2.ID", "Table2.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"ID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewID", "ID"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Table2.ID"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Value 1"}, {"Table2.Value", "Value 2"}})
in
    #"Renamed Columns1"

Попробуй это:

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"

Другой путь

let Source = Table.AddColumn(Table1,"Table", each 1) & Table.AddColumn(Table2,"Table", each 2),
#"Grouped Rows" = Table.Group(Source, {"ID", "Value"}, {{"data", each _, type table}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Expanded data" = Table.ExpandTableColumn(#"Added Index", "data", {"Table"}, {"Table"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Table", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Table", type text}}, "en-US")[Table]), "Table", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in  #"Removed Columns"