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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

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.

June 2025 community update carousel

Fabric Community Update - June 2025

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