Merge two table with two columns OR condition

Just apply two seperate merges for each column and then combine the results

Hi emohammad ,

Since a table cannot have duplicate column names, I have merged the two tables based on your conditions and filled in the missing Employee_ID and Email. I have also removed the duplicate columns in the final result.

Please try:

let
    test1 = #table(
        {"Employee_Id", "Email", "Department", "City", "Manager"},
        {
            {22221, "[email protected]", "Dept1", "City1", "Manager1"},
            {22222, "[email protected]", "Dept1", "City1", "Manager1"},
            {22223, null, "Dept1", "City2", "Manager1"},
            {22224, "[email protected]", "Dept2", "City2", "Manager2"},
            {22225, "[email protected]", "Dept2", "City2", "Manager2"},
            {22226, "[email protected]", "Dept2", "City3", "Manager3"},
            {22227, "[email protected]", "Dept2", "City3", "Manager3"}
        }
    ),
    test2 = #table(
        {"Employee_Id", "Email", "Grade", "Expertise", "Qualification"},
        {
            {22221, "[email protected]", "Grade1", "Expertise1", "Qualification1"},
            {null, "[email protected]", "Grade1", "Expertise1", "Qualification1"},
            {22223, "[email protected]", "Grade1", "Expertise2", "Qualification1"},
            {22224, null, "Grade2", "Expertise2", "Qualification2"},
            {22225, "[email protected]", "Grade2", "Expertise3", "Qualification3"},
            {22226, "[email protected]", "Grade3", "Expertise3", "Qualification3"},
            {22227, "[email protected]", "Grade3", "Expertise4", "Qualification3"}
        }
    ),
    MergedTables = Table.NestedJoin(test1, {"Employee_Id"}, test2, {"Employee_Id"}, "test2", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "test2", {"Email"}, {"test2_Email"}),
    AddedColumn = Table.AddColumn(ExpandedTable, "Filled_Email", each if [Email] = null then [test2_Email] else [Email]),
    NewTest1 = Table.RemoveColumns(AddedColumn, {"Email", "test2_Email"}),
    MergedTables2 = Table.NestedJoin(test2, {"Employee_Id"}, test1, {"Employee_Id"}, "test1", JoinKind.LeftOuter),
    ExpandedTable2 = Table.ExpandTableColumn(MergedTables2, "test1", {"Email"}, {"test1_Email"}),
    AddedColumn2 = Table.AddColumn(ExpandedTable2, "Filled_Email", each if [Email] = null then [test1_Email] else [Email]),
    NewTest2 = Table.RemoveColumns(AddedColumn2, {"Email", "test1_Email"}),
    JoinTables=Table.NestedJoin(NewTest1,{"Filled_Email"},NewTest2,{"Filled_Email"},"NewTest2",JoinKind.FullOuter),
    #"Expanded NewTest2" = Table.ExpandTableColumn(JoinTables, "NewTest2", {"Grade", "Expertise", "Qualification"}, {"Grade", "Expertise", "Qualification"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewTest2",{{"Filled_Email", "Email"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Employee_Id", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Employee_Id", "Email", "Department", "City", "Manager", "Grade", "Expertise", "Qualification"})
in
    #"Reordered Columns"

Best Regards,

Bof