Retrive value of the first column looking at the values in the rest of the row

[simon_boniz]

Hi All,

I have 2 tables, not linked to eachother (they cannot be linked).

Table 1 contain the database and all values are unique.

Table 2 contains the observation made. for each observation I want to look into table 1 is the observation is in in there and if yes I want name1 returned. 

Table 2 may can contain dulicates.

Has anyone any suggestions?

[simon_boniz]

I m not sure about pivoting and unpivoting the tables. I did not quite understand it. 

so this is what I did

if(ISBLANK(LOOKUPVALUE(name1,name1,Observation) == FALSE(),LOOKUPVALUE(Name1,Name1,Observation),

if(ISBLANK(LOOKUPVALUE(Name1,Name2,Observation)) == FALSE(),LOOKUPVALUE(Name1,Name2,Observation),

#Repeat for the other columns,

#finally use blank() to leave a blank cell in case a value was not found.

blank()))))

[amitchandak]

@simon_boniz , You can unpivot table 2, You will have the name and observation. Then you join them in model and use in visual

or merge in power query

https://radacad.com/pivot-and-unpivot-with-power-bi

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query