Forum Discussion
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 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 |
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 ?
4 Replies
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]
Hi mattramirez2020 ,
Check this file: Download PBIX
I assigned a new id for a combination Product Type & Product ID using Power Query.
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?
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 ?
