Get data from second table based on two columns (formula for Custom Column added)

[CuriousGeorge]

Hi PowerBI members... first post here, I'm a bit desperate. Trying to figure out how to get a value from one table based on two columns in another:

Table 1 has the make and model in different columns, table2 has all possible Models as seperate colums with relative values which I want to populate into a new column on table1. I'm hoping to get the green value/column populated in the example below

table1

Name| Make   | Model   |  NewCol

Test   | Make1 | Model3 |  789

table2

Make  |Model1 |Model2 | Model3

Make1| 123     |  456     |  789

Make2| 001     |  002     |  003

Please if anybody is able to assist 🙏

[PattemManohar]

@CuriousGeorge If you want to do it DAX then please use below to unpivot the lookup table

Test96LkpUnPivot = 
UNION(
 SELECTCOLUMNS(Test96Lkp,"Make",Test96Lkp[Make],"Type","Model1","Value",Test96Lkp[Model1])
,SELECTCOLUMNS(Test96Lkp,"Make",Test96Lkp[Make],"Type","Model2","Value",Test96Lkp[Model2])
,SELECTCOLUMNS(Test96Lkp,"Make",Test96Lkp[Make],"Type","Model3","Value",Test96Lkp[Model3])
)

Now the table will be like this..

Then, add a new field in your main table with looking up on above table as below

LkpVal = LOOKUPVALUE(Test96LkpUnPivot[Value],Test96LkpUnPivot[Make],Test96[Make],Test96LkpUnPivot[Type],Test96[Model])

[Anonymous · Accepted solution]

Easiest Way to get Table 2 to look like Table1 is click on Table2, click on Edit Query, Select all the Model columns, Right click, Unpivot columns

DAX way will become more and more complicated if you've got a lot of models. see example

https://ufile.io/qdmot