How to add a new column in a table based on another table without merge tables

[ZHC]

Hello All

I have a table A with all customers accounts and company names. However, due to acquisition/merging/demerging, some company names are changed. Table B has all the changed company names. As shown below.

because of reporting requirements, I don't want to replace the company name in Table A. I want to add a new column "Updated name" in Table A, on condition that if account # in table B, take the company name in Table B, otherwise keep the company name in table A. Unfortunately there are empty company name in Table A. 

Is it achievable without merging tables? I used CONTAINSSTRING

 Updated name = if( CONTAINSSTRING('Table A'[Account No], 'Table B'[Account No]), 'Table B'[Account new],  'Table A'[Company])

But I got error "A single value for column 'Account No' in table B cannot be determined."

Can anybody help me solve the issue. Many thanks in advance.

[PhilipTreacy · Accepted solution]

Hi @ZHC 

Download PBIX file with example below

This will create the column in TableA

Updated Name = IF(RELATED(TableB[Account No]) = [Account No], RELATED(TableB[Company Name]), 'TableA'[Company])

Regards

Phil

[ZHC]

It works perfectly. Thank you very much.