Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 )
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 )
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |