Forum Discussion

kaym's avatar
kaym
Helper I
2 years ago
Solved

Merge two tables without multiple matches

Hello,
I am trying to join two tables (origin and destination) in power bi. There are some cases where it is creating multiple matches one value.
For example, the origin table has:

SHIPMENT NUMBERSTOP NUMBER
36986STOP 1
36986STOP 2
36986STOP 3

 

the destination table has:

SHIPMENT NUMBERSTOP NUMBER
36986STOP 2
36986STOP 3
36986STOP 4


Now when I join i WANT this case to look like below:

SHIPMENT NUMBERSTOP NUMBERSTOP NUMBER (DESTINATION)
36986STOP 1STOP 2
36986STOP 2STOP 3
36986STOP 3STOP 4


But when I do an inner join it gives me more than just 3 rows. It looks like this:

SHIPMENT NUMBERSTOP NUMBERDESTINATION. STOP NUMBER
36986STOP 1STOP 2
36986STOP 2STOP 2
36986STOP 3STOP 2
36986STOP 1STOP 3
36986STOP 2STOP 3
36986STOP 3STOP 3
36986STOP 1STOP 4
36986STOP 2STOP 4
36986STOP 3STOP 4


I Dont't want multiple matches. Could someone please help me out? I would really really  appreciate any guidance. Been stuck on this for a while now.

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    Generally data is not so simple in BI world...

    The simplest solution is to insert an index in both the tables and do the merge on those 2 indexes and then remove indexes.

    If rows match one on one for Shipment Number in both the tables, then above would work perfectly.

    Otherwise create groups on Shipment Number, then perform better representative sample data.

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    Generally data is not so simple in BI world...

    The simplest solution is to insert an index in both the tables and do the merge on those 2 indexes and then remove indexes.

    If rows match one on one for Shipment Number in both the tables, then above would work perfectly.

    Otherwise create groups on Shipment Number, then perform better representative sample data.