Two key columns (one text and one number)

[mattramirez2020]

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 Type	Product ID
Left Product	1
Right Product	1
Left Product	2
Right Product	2

Products

Product Type	Product ID	Description	Cost
Left Product	1	desc info	5
Right Product	1	desc info 2	7
Left Product	2	desc info 4	3
Right Product	2	desc info 3	2

[amitchandak]

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

Hi @mattramirez2020 ,

Check this file: Download PBIX 

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

[mattramirez2020]

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

[camargos88 · Accepted solution]

@mattramirez2020 .

Check this code:

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 ?