Forum Discussion
Solved
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
| Name | Code | Date |
| Adam | C | 2/25/2019 |
| Brock | A | 8/3/2019 |
| Caleb | C | 7/6/2020 |
| Josh | B | 6/1/2020 |
| John | D | 4/5/2021 |
| Sarah | A | 5/15/2021 |
| Andrew | E | 12/12/2019 |
Table B
| Code | 2019 | 2020 | 2021 |
| A | 1 | 4 | 3 |
| B | 2 | 5 | 4 |
| C | 3 | 6 | 5 |
| D | 4 | 7 | 6 |
| E | 5 | 8 | 7 |
New Column Results
| Name | Code | Date | Value |
| Adam | C | 2/25/2019 | 3 |
| Brock | A | 8/3/2019 | 1 |
| Caleb | C | 7/6/2020 | 6 |
| Josh | B | 6/1/2020 | 5 |
| John | D | 4/5/2021 | 6 |
| Sarah | A | 5/15/2021 | 3 |
| Andrew | E | 12/12/2019 | 5 |
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.
2 Replies
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!
