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

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

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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