cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PMBA
New Member

how do I format data from SharePoint lookup fields?

I'm building a flow to report out of a SharePoint list on-demand and email an HTML Table to a set of recipients. Several of the fields I need to include in the Table are lookup fields to other lists within the SharePoint site.    i.e. "Process Name" which goes to the Process list and pulls the [Title] column.  

PMBA_1-1617900117035.png

 

When I pull in items and embed the HTML table into an email I get back code for the lookup column 

 

PMBA_2-1617900585114.png

how can I "clean this up" so it's only showing the "process name" ? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User
Super User

Hello @PMBA ,

since it's in the 'Create html table' action processing a multiple choice column, you'll need a fancy combination of expressions to take only 'Value' from all choices in the column and convert them into a string.

join(xpath(xml(json(concat('{"body":{"value":', item()?['Choice'], '}}'))), '/body/value/Value/text()'), ', ')

Note:
replace Choice with your column internal name

 I wrote a post on building the expression if you're interested, it's a bit complex to explain it directly in each post.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

1 REPLY 1
tom_riha
Super User
Super User

Hello @PMBA ,

since it's in the 'Create html table' action processing a multiple choice column, you'll need a fancy combination of expressions to take only 'Value' from all choices in the column and convert them into a string.

join(xpath(xml(json(concat('{"body":{"value":', item()?['Choice'], '}}'))), '/body/value/Value/text()'), ', ')

Note:
replace Choice with your column internal name

 I wrote a post on building the expression if you're interested, it's a bit complex to explain it directly in each post.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,551)