Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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"
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |