cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User IV
Super User IV

@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]



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User III
Super User III

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!



View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors