Forum Discussion
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...
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
Anonymous,
Thanks for your help. Can you please modify the above code to get the same result as shown in the final result table? Please show the duplicate columns and I want to see the gaps to highlight them in the reports.
Note:
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
Thanks, @v-bofeng-msft. This worked.
If thare are nulls in the test1.Employee_Id, I need to add a step to remove duplicates.