Lookup Columns and Power Automate

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): 

  1. Click into the lookup field and select the Expression tab.
  2. Type "if(" - this will automatically add the closing bracket.
  3. Type "empty(" - this will automatically add the closing bracket.
  4. Select the column you want to copy from the Dynamic content tab.
  5. Click one space to the right and add ",".
  6. Enter a single quotation mark. This will automatically add the closing ' - this means that '' is the value if the source lookup is empty. 
  7. Click one to the right again and add ",".
  8. Type "Concat(" and click one space to the right.
  9. Enter a single quotation mark and "systemusers(" or whatever the table set name is.
  10. Click to the right after the closing ' and add ",".
  11. Select the source column value from the Dynamic content tab.
  12. Add a ",".
  13. Enter a single quotation mark and ")".

The Expression should look something like the below:

if(empty(outputs('Get_Source_Project')?['body/_bw_projectmanager_value']),'',concat('systemusers(',outputs('Get_Source_Project')?['body/_bw_projectmanager_value'],')'))

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.