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
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
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.