Forum Discussion
Joining Two Tables With Partial Matching Values
Hello everyone,
So diving into my problem right away, I need to join two tables with partially matching values, the example should explain it. I was hoping there could be some kind of Merge at the query level that could get this done but so far haven't been able to figure it out.
Table 1
Col. A - Col. B
1 - A
1 - B
2 - D
3 - F
Table 2
Col. A - Col. B
1 - A
1 - C
2 - D
2 - E
3 - F
4 - G
Table 3 (combined result)
Col A - Col B (Table 1) - Column B (Table 2)
1 - A - A
1 - B - Null
1 - Null - C
2 - D - D
2 - Nulll - E
3 - F - F
4 - Null - G
Hi moizsherwani
this should be a simple full outer join with a custom column added at the end:
let Source = Table.NestedJoin(Table1,{"Col. A", "Col. B"},Table2,{"Col. A", "Col. B"},"Table2",JoinKind.FullOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Col. A", "Col. B"}, {"Table2.Col. A", "Table2.Col. B"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "id", each if [Col. A] = null then [Table2.Col. A] else [Col. A], Int64.Type), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"id", "Col. B", "Table2.Col. B"}) in #"Removed Other Columns"
2 Replies
Hi moizsherwani
this should be a simple full outer join with a custom column added at the end:
let Source = Table.NestedJoin(Table1,{"Col. A", "Col. B"},Table2,{"Col. A", "Col. B"},"Table2",JoinKind.FullOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Col. A", "Col. B"}, {"Table2.Col. A", "Table2.Col. B"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "id", each if [Col. A] = null then [Table2.Col. A] else [Col. A], Int64.Type), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"id", "Col. B", "Table2.Col. B"}) in #"Removed Other Columns"Hi LivioLanzo.
I found your solution for this problem, but i have a question...
Where should i insert the script that you posted? I have to create a new table?
Please help me with this. I have 2 tables and i need to join both.
Material Grupo N3
4G010096800 POLYFIT Barras 4G010096801 POLYFIT Barras 4G010096802 POLYFIT Bolas 4G010096803 POLYFIT Bolas 4G010096804 POLYFIT Bolas 4G010096805 POLYFIT Bolas Cod. Material Solic. Grupo Art. Jerarquía
90320091750 16 ECM1 EL03010201 9C010094405 1 EIMPCHN EL010202 9C010094405 1 EIMPCHN EL010202 9C010094405 3 EIMPCHN EL010202 9C060094913 1 EIMPCHN EL010202 9C060095108 1 EIMPCHN EL010202 9C060094912 2 EIMPCHN EL010201 91008000002 80 EIMP EL0504 9L000056456 80 EMAES EL02010206 9C010095579 1 EIMPCHN EL010201 9C030095740 1 EIMPCHN EL01010101 9C010095307 4 EIMPCHN EL010204 9C010006423 2 EIMPCHN EL010202 Best Regards
Juan
