Add column in Table A based on values being looked up in Table B

[EvelynN]

I'm struggling to do something that I feel should be fairly simple;

I have two tables Table A which is like this:

Ref 1	Ref 2
A	1
B	2

and Table B which is like this

	1	2
A	Value1	Value2
B	Value3	Value4

I want to add a column to Table A which uses the references to lookup what the value should be, so my end result in the case above would be

Ref 1	Ref 2	Lookup
A	1	Value1
B	2	Value4

But I can't quite seem to get what I want in a neat way. Any help would be really appriciated as it'sd driving me slightly crazy.

[FreemanZ]

This is not as simple as it seems.

Table B is matrix table, you would need to unpivot it first. Then you can link with Table A with the unpivoted table. 

check this,

https://learn.microsoft.com/en-us/power-query/unpivot-column

[NikhilChenna · Accepted solution]

Hi @EvelynN ,

You can acheive the above by the below solution,

1. You have to unpivot the second table. But i duplicated the table and created a table3/

2. Create a concart column in Table 1 with concat of (Ref1,Ref2).

3. Create a concat column in Table 3 with concat of (Ref1, Attribute).

4. Use Lookup function and lookup the data from table 3 to table1 based on the concat column.

Attaching the pbix link for your reference.

Lookup issue pbix link 

Here is the output 

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!