“I have a list of codes that must be translated into full names, how can I include such mapping table in my Power Automate flow?”
When you use Power Automate flows to import data, you can encounter a situation when the input doesn’t contain everything. Instead of getting the full information you might get just some id from the source system. The system knows what it means, but you don’t, and the flow neither. Yet you need the full name, not just some id that nobody will understand. How do you reflect this in the flow? What’s the best way to ‘translate’ it?
Create a mapping table
The approach you don’t want to use is to translate it in a loop. Processing each data row in a cycle would take ages even for a reasonably small amount of data, as already explained. Ideally you want to process them as quickly as possible, all at once with the ‘Select’ action. Yet if you stick to the ‘Select’ there’s no way for any preprocessing, everything must be done in the action using expressions. Meaning also that you should prepare the translations to be expression friendly – in a mapping table.
Mapping table is nothing more than an object variables where you put all the keys as property names and their values, as used in the past e.g. to translate month names or user id to user names.
Initialise the object variable and define all the possible keys and their values.
Use the mapping table
As already mentioned, since you want to replace all the values at once, the ‘Select’ action will be your best friend. In my example I have the user id in an Excel file – I’ll list all the rows and ‘Select’ them again for the translation.
But instead of selecting the values directly, use an expression to pick the corresponding value from the mapping table. Since the variable contains JSON object, you can pick only the desired value.
variables('varMappingTable')?[<keyValue>]Using the column with the user id instead of the placeholder:
variables('varMappingTable')?[item()?['End User']]
The outcome will take all the rows and replace the user id with the user name using the mapping table.
What about values missing in the mapping table?
Before you finish, there’s one more situation you should consider. As you’re creating the mapping table by yourself, there’s a chance that it won’t cover all values on the first try – if there’s no value the solution above will return ‘null’. It’s a good idea to keep at least some information if it isn’t there.
Instead of referencing the variable directly, put it in the coalesce(…) expression. That way you’ll always get some value in the column, let it be the translated or the original one.
coalesce(variables('varMappingTable')?[item()?['End User']], item()?['End User'])While the information won’t be in the desired format, it’ll be still there and you can deal with it later.
Summary
Mapping table is a powerful concept in Power Automate flows as there’re many situations where you might need to translate a value into a different one, let it be user id to user name, month number to local month name, status number to status name… in all these situations you might benefit from it. Instead of looking into an external source you can look into a single variable. Instead of filtering you can pick the desired value right away, replacing additional actions with a single expression. One mapping table and one ‘Select’ action can help you ‘translate’ thousands of data rows.
1 question about ‘Select’ function with ‘Json’
in your previous article that i’m following, it seems different UI between current one : there was only ‘Map’ text box but now it has ‘Key’ and ‘Value’ both :
(https://tomriha.com/identify-which-sharepoint-item-columns-were-updated-in-power-automate/#comment-678)
so I couldn’t know how to input ‘json(item())’ in ‘Select’.
Can you guide me how to ?
Hey, thanks! This was super helpful. Using an ‘Object’ variable to do key-value lookups saved me from doing a massive ugly if/else chain. This is so much cleaner. And the bit about coalesce() was also very neat, for returning the given key if there was no corresponding value to return. Good stuff, keep it up 🙂