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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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