Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |