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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kaym
Helper I
Helper I

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.

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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.

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors