When using Power Automate to set the value of a Lookup column, you must wrap the value in a lookup table plural value (classic name for this is Entity Set Name). To get this value, open the table in the Metadata Browser extension.
If the relevant column is a user lookup, the syntax to use would be as follows:
If there is a chance that the source lookup you are copying from is blank, you will have to use an expression to allow for this; otherwise the flow will fail when it tries to set a blank lookup. The syntax in this scenario would be entered as follows (do not type the double quotation marks unless noted otherwise):
- Click into the lookup field and select the Expression tab.
- Type "if(" - this will automatically add the closing bracket.
- Type "empty(" - this will automatically add the closing bracket.
- Select the column you want to copy from the Dynamic content tab.
- Click one space to the right and add ",".
- Enter a single quotation mark. This will automatically add the closing ' - this means that '' is the value if the source lookup is empty.
- Click one to the right again and add ",".
- Type "Concat(" and click one space to the right.
- Enter a single quotation mark and "systemusers(" or whatever the table set name is.
- Click to the right after the closing ' and add ",".
- Select the source column value from the Dynamic content tab.
- Add a ",".
- Enter a single quotation mark and ")".
The Expression should look something like the below:
If the flow does not present any error messages, you must still test the flow as Power Automate will allow you to enter a faulty expression. You can run a test from inside the flow.