Merge two table with two columns OR condition

[emohammad]

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 query in the power query? 

select * from 

test1 t1

full join test2 t2

on t1.Employee_Id=t2.Employee_Id OR t1.Email=t2.Email

test1
Employee_Id	Email	Department	City	Manager
22221	abc@gmail.com	Dept1	City1	Manager1
22222	cdf@gmail.com	Dept1	City1	Manager1
22223		Dept1	City2	Manager1
22224	jkl@gmail.com	Dept2	City2	Manager2
22225	mno@gmail.com	Dept2	City2	Manager2
22226	pqr@gmail.com	Dept2	City3	Manager3
22227	stu@gmail.com	Dept2	City3	Manager3

test2
Employee_Id	Email	Grade	Expertise	Qualification
22221	abc@gmail.com	Grade1	Expertise1	Qualification1
	cdf@gmail.com	Grade1	Expertise1	Qualification1
22223	ghi@gmail.com	Grade1	Expertise2	Qualification1
22224		Grade2	Expertise2	Qualification2
22225	mno@gmail.com	Grade2	Expertise3	Qualification3
22226	pqr@gmail.com	Grade3	Expertise3	Qualification3
22227	stu@gmail.com	Grade3	Expertise4	Qualification3

Final Result
EMPLOYEE_ID	EMAIL	DEPARTMENT	CITY	MANAGER	EMPLOYEE_ID	EMAIL	GRADE	EXPERTISE	QUALIFICATION
22221	abc@gmail.com	Dept1	City1	Manager1	22221	abc@gmail.com	Grade1	Expertise1	Qualification1
22222	cdf@gmail.com	Dept1	City1	Manager1		cdf@gmail.com	Grade1	Expertise1	Qualification1
22223		Dept1	City2	Manager1	22223	ghi@gmail.com	Grade1	Expertise2	Qualification1
22224	jkl@gmail.com	Dept2	City2	Manager2	22224		Grade2	Expertise2	Qualification2
22225	mno@gmail.com	Dept2	City2	Manager2	22225	mno@gmail.com	Grade2	Expertise3	Qualification3
22226	pqr@gmail.com	Dept2	City3	Manager3	22226	pqr@gmail.com	Grade3	Expertise3	Qualification3
22227	stu@gmail.com	Dept2	City3	Manager3	22227	stu@gmail.com	Grade3	Expertise4	Qualification3

[wdx223_Daniel]

[Anonymous]

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, "abc@gmail.com", "Dept1", "City1", "Manager1"},
            {22222, "cdf@gmail.com", "Dept1", "City1", "Manager1"},
            {22223, null, "Dept1", "City2", "Manager1"},
            {22224, "jkl@gmail.com", "Dept2", "City2", "Manager2"},
            {22225, "mno@gmail.com", "Dept2", "City2", "Manager2"},
            {22226, "pqr@gmail.com", "Dept2", "City3", "Manager3"},
            {22227, "stu@gmail.com", "Dept2", "City3", "Manager3"}
        }
    ),
    test2 = #table(
        {"Employee_Id", "Email", "Grade", "Expertise", "Qualification"},
        {
            {22221, "abc@gmail.com", "Grade1", "Expertise1", "Qualification1"},
            {null, "cdf@gmail.com", "Grade1", "Expertise1", "Qualification1"},
            {22223, "ghi@gmail.com", "Grade1", "Expertise2", "Qualification1"},
            {22224, null, "Grade2", "Expertise2", "Qualification2"},
            {22225, "mno@gmail.com", "Grade2", "Expertise3", "Qualification3"},
            {22226, "pqr@gmail.com", "Grade3", "Expertise3", "Qualification3"},
            {22227, "stu@gmail.com", "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

[emohammad]

@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:

[Anonymous · Accepted solution]

Hi @emohammad ,

Please try:

let
    test1 = #table(
        {"Employee_Id", "Email", "Department", "City", "Manager"},
        {
            {22221, "abc@gmail.com", "Dept1", "City1", "Manager1"},
            {22222, "cdf@gmail.com", "Dept1", "City1", "Manager1"},
            {22223, null, "Dept1", "City2", "Manager1"},
            {22224, "jkl@gmail.com", "Dept2", "City2", "Manager2"},
            {22225, "mno@gmail.com", "Dept2", "City2", "Manager2"},
            {22226, "pqr@gmail.com", "Dept2", "City3", "Manager3"},
            {22227, "stu@gmail.com", "Dept2", "City3", "Manager3"}
        }
    ),
    test2 = #table(
        {"Employee_Id", "Email", "Grade", "Expertise", "Qualification"},
        {
            {22221, "abc@gmail.com", "Grade1", "Expertise1", "Qualification1"},
            {null, "cdf@gmail.com", "Grade1", "Expertise1", "Qualification1"},
            {22223, "ghi@gmail.com", "Grade1", "Expertise2", "Qualification1"},
            {22224, null, "Grade2", "Expertise2", "Qualification2"},
            {22225, "mno@gmail.com", "Grade2", "Expertise3", "Qualification3"},
            {22226, "pqr@gmail.com", "Grade3", "Expertise3", "Qualification3"},
            {22227, "stu@gmail.com", "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

[emohammad]

Thanks, @v-bofeng-msft. This worked.

If thare are nulls in the test1.Employee_Id, I need to add a step to remove duplicates. 

[Omid_Motamedise]

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