Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moizsherwani
Continued Contributor
Continued Contributor

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

 

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

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!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

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!  

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

4G010096800POLYFITBarras
4G010096801POLYFITBarras
4G010096802POLYFITBolas
4G010096803POLYFITBolas
4G010096804POLYFITBolas
4G010096805POLYFITBolas

 

Cod. Material           Solic.    Grupo Art.        Jerarquía

9032009175016ECM1EL03010201
9C0100944051EIMPCHNEL010202
9C0100944051EIMPCHNEL010202
9C0100944053EIMPCHNEL010202
9C0600949131EIMPCHNEL010202
9C0600951081EIMPCHNEL010202
9C0600949122EIMPCHNEL010201
9100800000280EIMPEL0504
9L00005645680EMAESEL02010206
9C0100955791EIMPCHNEL010201
9C0300957401EIMPCHNEL01010101
9C0100953074EIMPCHNEL010204
9C0100064232EIMPCHNEL010202

 

Best Regards

Juan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.