Forum Discussion
Get data from second table based on two columns (formula for Custom Column added)
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 🙏
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
2 Replies
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
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])
