Joining Two Tables With Partial Matching Values

[moizsherwani]

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

[LivioLanzo · Accepted solution]

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"

[JuanVR10]

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