cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Joining Two Tables With Partial Matching Values

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"

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

1 REPLY 1
Super User
Super User

Re: Joining Two Tables With Partial Matching Values

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"

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!