Replace all values in column using a another table to store transformations/translations

[OKgo]

Translation Table (All rows are unique). 
The numbers assigned do not have a strong logic. A look up would be required.

Location New_Location

World	02 World
Argentina	07 Argentina

Data (Many duplicates)

Location Value

World	5
World	6
Mexico	18

I would like to replace the Data[Location] with Translation[New_Location]. If there is no entry in the translation table the orignal entry is acceptable. Within the model a many to one relationship between Data[Location] and Translation[Location] exisits. There is too many values to make replace values feature practical and the translation table is subject to changes.

In Excel, I would have created a VLOOKUP helper column. Then copy and paste-special-values to overwrite the orignal location Data[Location] entries. I am strugling through these fourms and google to find an elgant solution. Thank you very much in advance for your time! 

[Zubair_Muhammad · Accepted solution]

Hi @OKgo

Try this calculated column in DataTable

New Location =
VAR RESULT =
    CALCULATE ( FIRSTNONBLANK ( TranslationTable[New_Location], 1 ) )
RETURN
    IF ( ISBLANK ( RESULT ), 'DataTable'[Location], result )

[Zubair_Muhammad]

@OKgo

[OKgo]

Hi Zubair_Muhammad. Thank you so much for replying. Am I using the correct window to use the code? The show error feature highlights the word RESULT.

[Zubair_Muhammad]

Yes this is wrong window.

The formula is for a calculated column

Right click the table, select new column and then use this formula

[OKgo]

Thank you so much! It works in the report view.

I was hoping to be able to see the column within edit queries to check the output but maybe that is not possible.