Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
adoster
Resolver I
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.

Tables are linked via "Code"

 

Table A

NameCodeDate
AdamC2/25/2019
BrockA8/3/2019
CalebC7/6/2020
JoshB6/1/2020
JohnD4/5/2021
SarahA5/15/2021
AndrewE

12/12/2019

 

Table B

Code201920202021
A143
B254
C365
D476
E587

 

New Column Results

NameCodeDateValue
AdamC2/25/20193
BrockA8/3/20191
CalebC7/6/20206
JoshB6/1/20205
JohnD4/5/20216
SarahA5/15/20213
AndrewE12/12/20195

 

 

1 ACCEPTED SOLUTION
ddpl
Solution Sage
Solution Sage

@adoster 

 

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.

View solution in original post

2 REPLIES 2
ddpl
Solution Sage
Solution Sage

@adoster 

 

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.

This works great! THANK YOU!

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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