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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.