How to Copy a SharePoint Lookup Value to a Text Column with Power Automate
Question details
The user needs to extract the plain text value from a SharePoint lookup column and duplicate it into a single-line text column using Power Automate.
- Product
- Power Automate and SharePoint
- Device & OS
- not provided
- Scenario
- Automating SharePoint list data processing where a related lookup item needs to be recorded as a static plain text string in a destination list.
- Observed behavior
- By default, the lookup column returns a complex object or the lookup ID rather than the displayed text string, causing the destination text column to populate incorrectly.
Verify that you have Edit permissions for the destination SharePoint list and the appropriate licensing to create and run Power Automate flows.
Map the Lookup Value Dynamic Content in Update Item
This standard approach uses the 'Update item' action to map directly to the lookup string value rather than the lookup ID object.
When retrieving data from a SharePoint list, Power Automate distinguishes between the Lookup ID and the actual Lookup Value. Mapping the correct dynamic content ensures plain text is transferred.
In your destination SharePoint list, click 'Add column', select 'Single line of text', and name it appropriately to store the extracted value.
Open your Power Automate flow and add the 'Get items' action to retrieve the records from your source SharePoint list.
Insert an 'Apply to each' action and pass the 'value' array output from the 'Get items' action into it.
Inside the loop, add the 'Update item' action. Click on the field for your single-line text column, open the dynamic content menu, and select 'Your_Lookup_Column_Name Value' (avoiding the one that lacks 'Value', which is just the ID).
Use OData Queries to Expand the Lookup Field
For complex or nested lookups, use an OData query in the Get items action to explicitly request the lookup string from the related list.
Troubleshoot Interfering Column Formatting
Ensure that SharePoint column formatting JSON is not masking the true underlying data types.
Seamlessly Manage and Analyze Exported Data with WPS Office
While Power Automate and SharePoint are excellent for automated list management, analyzing exported list data often requires a powerful spreadsheet tool. WPS Office offers a free, lightweight, and highly compatible suite that perfectly replaces Microsoft Office for your daily document and data needs.
Frequently Asked Questions
Why does my flow insert a number instead of the text from the lookup column?
By default, Power Automate frequently pulls the underlying Lookup ID instead of the display text. To fix this, open the dynamic content menu in your 'Update item' action and ensure you select the property labeled 'Value' next to your lookup column name.
What should I do if the Lookup Value dynamic content is missing in Power Automate?
If the dynamic content does not show the 'Value' option, try saving and refreshing your flow. Alternatively, use an expression such as `items('Apply_to_each')?['YourLookupColumnName']?['Value']` to manually target the text.
Can I use SharePoint column formatting JSON to extract the lookup text instead of Power Automate?
No. SharePoint column formatting only alters how data is visually displayed in the browser. It does not change the actual underlying data stored in the database, meaning exported data or other columns will still see the raw lookup object.
