How to look up values in TB2 if a value in a column in TB1 matches with a field name in TB2

[acao-96]

Hello,

I wonder if there is a way in PowerBI to look up values by searching column names to return results in the matching column.

For example:

TB1:

ID	Year
1	Year-1
1	Year-2
2	Year 0
3	Year-1

TB2:

ID	Year 0	Year-1	Year-2
1	66	68	21
2	35	59	54
3	47	78	87

Desired results:

ID	Year	Qty
1	Year-1	68
1	Year-2	21
2	Year 0	35
3	Year-1	78

Any suggestions would be appreciated!

[v-easonf-msft · Accepted solution]

Hi, @acao-96 

If there are not many values in the year field, you can create a calculated column in TB1 as below:

Calculated column：

Dax Qty =
SWITCH (
    Table1[Year],
    "Year 0", LOOKUPVALUE ( Table2[Year 0], Table2[ID], Table1[ID] ),
    "Year-1", LOOKUPVALUE ( Table2[Year-1], Table2[ID], Table1[ID] ),
    "Year-2", LOOKUPVALUE ( Table2[Year-2], Table2[ID], Table1[ID] )
)

result：

Best Regards,
Community Support Team _ Eason

[Fowmy]

@acao-96 

UnPivot feature in Power Query to convert the columns for years in the TB2 table into rows then you can simplky merge or use DAX to calculate the results.

Video: https://www.youtube.com/watch?v=Vff2kRBM95o

[acao-96]

Thank you for your response! I was aware of the Unpivot method but I was wondering if there was a direct way to get that column in Dax instead of having to unpivot the TB2 first and then merge to get the results.

[Fowmy]

@acao-96 

Check if this could help: 
https://www.youtube.com/watch?v=9Xv8COs59tc