Vlookup from two tables and match both results in powerbi

[Anonymous]

Hello, 

I want to match two column in both tables from data to key and return the cost column into the data table.

I have a key table

Type    Type 2        Cost

40         Half            80

40         Full           150

20         Full             40

20         Half            90

I have a data table

It lists similar to the key but doesnt have cost. I would like to match the two columns from the key table and return the cost in the data table. 

Type   Type 2

40        Half

20       Half

40      Full

etc      etc

Output : In Data Table 

Type   Type 2   Cost

40        Half       80

20       Half        40

40      Full        150

20      Full          90

I have a relationship between Key Table - Type ( Many to Many) to Data Table - Type. 

[jdbuchanan71]

@Anonymous 

You could create a new column that is 'Type' & 'Type 2' on both tables and join it like that.

You would add the Type_Key filed to both tables and join the using that field.

Assuming the entries on your key table are unique based on Type & Type2 the join should be a *:1 and then you can use the RELATED function to pull the data.

[johnt75 · Accepted solution]

You could add a column onto the Data table like

Cost = LOOKUPVALUE( 'Key'[Cost], 'Key'[Type], 'Data'[Type], 'Key'[Type 2], 'Data'[Type 2])

[Anonymous]

Thanks very much, this worked perfectly!