cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors