Need Help With Dataset With A Mapping Table To Create New Columns

[Starstorm]

Hi,

Basically, I am trying to map the "Mapping Table" to my "Primary Data".

Here are the dummy data to illustrate the logic intended. As you can see, the columns highlighted in grey are the variables for Primary Data, which are also found in Mapping Table as the conditions.

How it should work? All 3 variables from primary data must tally with mapping table in order to output the respective country and model. For instance, for the first row of the data 11111, Seagate and Hard drive - it will give me US and A respectively [as illustrated in End Result].

So here are my queries:

1. Of course, the easiest way I could think of is just to CONCATENATE these 3 variables into a single string - all mapping table strings are unique, and then create a relationship with Primary Data. However, is there a more 'proper' way of doing it in Power BI instead of doing CONCATENATE?
2. For my mapping table, you will realize for Account Number 99999 - it does not have the Brand and Product. This is intentional because I want to also create a rule whereby so long the account number is 99999, it will become NK and Q regardless of the Brand and Product. However, my method of [1] will not work for [2] because 99999 because my mapping table will not be able to find the common string at primary data table. Is there a way to resolve this having both [1] and [2]?

This is just a dummy data; in actuality, I have over 60+ different brands, and 60+ different products - so it is not possible for me to create multiple rows.

Please let me know if you need more information to assist my case here.

Thank you, and appreciate your support and guidance.

[johnt75 · Accepted solution]

I think you can create a column in both tables like

Mapping Column = IF ( 'Table'[Account number] = "99999", 'Table'[Account number],
COMBINEVALUES( "|", 'Table'[Account number], 'Table'[Brand], 'Table'[Product])
) 

where 'Table' would be the table you are creating the column in. You should then be able to create a one-to-many relationship from your mapping table to your primary data