Forum Discussion

emohammad's avatar
emohammad
Frequent Visitor
1 year ago
Solved

Merge two table with two columns OR condition

Hi, I need to merge two tables with two columns using an OR condition on a power query. Below are the two sample tables and expected results.  Can anyone suggest a way to perform a similar SQL quer...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi emohammad ,

     

    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, "Table1ID", each if [Email] = null then [test2_Email] else [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, "Table2ID", each if [Email] = null then [test1_Email] else [Email]),
    JoinTables=Table.NestedJoin(AddedColumn,{"Table1ID"},AddedColumn2,{"Table2ID"},"NewTest2",JoinKind.FullOuter),
    #"Expanded NewTest2" = Table.ExpandTableColumn(JoinTables, "NewTest2", {"Employee_Id","Email","Grade", "Expertise", "Qualification"}, {"Test2.Employee_Id","Test2.Email","Grade", "Expertise", "Qualification"}),
    RemoveColumns = Table.RemoveColumns(#"Expanded NewTest2",{"test2_Email","Table1ID"}),
    #"Sorted Rows" = Table.Sort(RemoveColumns,{{"Employee_Id", Order.Ascending}})
in
    #"Sorted Rows"

    Best Regards,

    Bof