cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Create new Column: values based on column header (year) from another table

Hello,

I'm trying to create a new column with values from another table, based on an ID & the column header which is a date/year value.

Table A

 Name Code Date Adam C 2/25/2019 Brock A 8/3/2019 Caleb C 7/6/2020 Josh B 6/1/2020 John D 4/5/2021 Sarah A 5/15/2021 Andrew E 12/12/2019

Table B

 Code 2019 2020 2021 A 1 4 3 B 2 5 4 C 3 6 5 D 4 7 6 E 5 8 7

New Column Results

 Name Code Date Value Adam C 2/25/2019 3 Brock A 8/3/2019 1 Caleb C 7/6/2020 6 Josh B 6/1/2020 5 John D 4/5/2021 6 Sarah A 5/15/2021 3 Andrew E 12/12/2019 5

1 ACCEPTED SOLUTION
Solution Sage

Create a calculated column...

Value = SWITCH(TRUE(),
FORMAT('Table A'[Date], "yyyy") = "2019", LOOKUPVALUE('Table B'[2019]'Table B'[Code]'Table A'[Code]),
FORMAT('Table A'[Date], "yyyy") = "2020", LOOKUPVALUE('Table B'[2020], 'Table B'[Code], 'Table A'[Code]),
FORMAT('Table A'[Date], "yyyy") = "2021", LOOKUPVALUE('Table B'[2021], 'Table B'[Code], 'Table A'[Code])
)

Hope it will work for you.
2 REPLIES 2
Solution Sage

Create a calculated column...

Value = SWITCH(TRUE(),
FORMAT('Table A'[Date], "yyyy") = "2019", LOOKUPVALUE('Table B'[2019]'Table B'[Code]'Table A'[Code]),
FORMAT('Table A'[Date], "yyyy") = "2020", LOOKUPVALUE('Table B'[2020], 'Table B'[Code], 'Table A'[Code]),
FORMAT('Table A'[Date], "yyyy") = "2021", LOOKUPVALUE('Table B'[2021], 'Table B'[Code], 'Table A'[Code])
)

Hope it will work for you.
Resolver I

This works great! THANK YOU!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.