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
mattramirez2020
Helper II
Helper II

Two key columns (one text and one number)

Hi, I am relatively new to Power BI. I have sales data with a product ID and a product type that I am trying to build a relationship to a product table. The product numbers repeat across the categories so they are not unique by themself. I know I could join the text name and the number ID but I have heard that relationships load faster if they use only numbers. Is there a better way beyond merging the columns to create this relationship? I have searched on youtube and here and I am afraid I am not searching using the correct terminology or missing an obvious solution! Any help would be appreciated.

 

Sales

Product TypeProduct ID
Left Product1
Right Product1
Left Product2
Right Product2

 

Products

Product TypeProduct IDDescriptionCost
Left Product1desc info5
Right Product1desc info 27
Left Product2desc info 43
Right Product2desc info 32
1 ACCEPTED SOLUTION

@mattramirez2020 .

 

Check this code:

Capture.PNG

 

However, if you have products on sales that is not on products table you have more works to do, like compare and add them to products table. Do you have this situation ?



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

Proud to be a Super User!



View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@mattramirez2020 , In M or Dax You can create a combined column in both tables and join on them

new column

Key = [Product Type] & " " & [Product ID]

camargos88
Community Champion
Community Champion

Hi @mattramirez2020 ,

 

Check this file: Download PBIX 

 

I assigned a new id for a combination Product Type & Product ID using Power Query.



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

Proud to be a Super User!



@camargos88 Thank you for helping out! What you did makes perfect sense but I just can't figure out how to do it. I was able to assign all the products a unique ID/key but I can't figure out how you map back sales to a new key/id that did not previously exist?

@mattramirez2020 .

 

Check this code:

Capture.PNG

 

However, if you have products on sales that is not on products table you have more works to do, like compare and add them to products table. Do you have this situation ?



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

Proud to be a Super User!



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.

Top Solution Authors
Top Kudoed Authors