Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey,
I have several tables with columns which has values (oftenly numbers) that I would like to translate/replace with other values (oftenly text), for instance gender, level, status... See example below, translations tables,
and many other tables with such columns as gender, level... that need to be translated according to my translation tables above.
What is the best solution in this case, I'm searching for a generic solution which I can reuse on several tables and for different columns. Like a function which takes as input (column to be translated in whatever table, translation table) and returns a column in the same table with the translated values.
Thanks,
You could do this by having a lookup table joined to the fact table and then use the RELATED function in a calculated column.
Or you could use the replace in Power Query Editor.
Or you could use the SWITCH function.
Thanks for your respond.
I'm not sure but I think all your suggestions are good in case of one fact table, in my case I have several tables and I want to avoid repeating same steps for each table, like joining tables and writing a specific function for a specific table, or using replace which means doing that for each column and each table, or Switch which also mean manually writing down values with their replacments several times.
I have about 30 fact tables where each one have at least one column to be translated...
You could also use LOOKUPVALUE, but they would need to be the same datatype, I believe. You are going to have to choose to do something with these tables to get the columns you need added. Are you loading lookup tables into your model?
yes
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |