Forum Discussion
Replace values in multiple columns with values in another table based on ID
Hi everyone,
I have one main table and another, smaller table with up-to-date information for some of the IDs in the first table. I'm trying to overwrite/replace the out-of-date data with the up-to-date data while maintaining everything else.
Table A:
ID, Year, Amount, Country
A, 2000, null, US
B, 2001, $40, US
null, 2002, $40, US
D, null, $40, US
Table B:
ID, Year, Amount
A, 2020, $50
B, 2021, $50
D, 2023, $50
Table A after replacing with values from Table B:
ID, Year, Amount, Country
A, 2020, $50, US
B, 2021, $50, US
null, 2002, $40, US
D, 2023, $50, US
Basically, "if ID in Table B exists in Table A, replace data in Table A with data from Table B." Any help would be greatly appreciated as most of the answers I see here are for replacing nulls or other consistent values in one column.
Hi bharri
You can add two new columns for Updated Year and Updated Amount in Table A by using the below measures:
Updated Year = IF ( ISBLANK ( LOOKUPVALUE ( 'Table B'[ Year], 'Table B'[ID], 'Table A'[ID] ) ), 'Table A'[ Year], LOOKUPVALUE ( 'Table B'[ Year], 'Table B'[ID], 'Table A'[ID] ) )Updated Amount = IF ( ISBLANK ( LOOKUPVALUE ( 'Table B'[ Amount], 'Table B'[ID], 'Table A'[ID] ) ), 'Table A'[ Amount], LOOKUPVALUE ( 'Table B'[ Amount], 'Table B'[ID], 'Table A'[ID] ) )The Output will be as below:
Create calculated columns in Power BI Desktop
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos !!
1 Reply
- VahidDM
Super User
Hi bharri
You can add two new columns for Updated Year and Updated Amount in Table A by using the below measures:
Updated Year = IF ( ISBLANK ( LOOKUPVALUE ( 'Table B'[ Year], 'Table B'[ID], 'Table A'[ID] ) ), 'Table A'[ Year], LOOKUPVALUE ( 'Table B'[ Year], 'Table B'[ID], 'Table A'[ID] ) )Updated Amount = IF ( ISBLANK ( LOOKUPVALUE ( 'Table B'[ Amount], 'Table B'[ID], 'Table A'[ID] ) ), 'Table A'[ Amount], LOOKUPVALUE ( 'Table B'[ Amount], 'Table B'[ID], 'Table A'[ID] ) )The Output will be as below:
Create calculated columns in Power BI Desktop
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos !!