cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MuFeR
Frequent Visitor

Flow to concatenate all SP column values in a single row.

Got a simple yet tricky flow I'm stuck with.

Let's say I got the following SP list with 3 columns (all of type SingleLineOfText). 

 

ColorDayPet
BlackMondayDog
RedSundayCat
YellowSaturdayParrot

 

Output i'm trying to get is all the values of each individual column separated by "/".

So for above example I want to do a "Get items" from SharePoint and then add a row in an existing excel file which should look like this.

ColorDayPet
Black/Red/YellowMonday/Sunday/SaturdayDog/Cat/Parrot

 

Only way I figured out would be to initialize a string for every single column (varColor, varDay, varPet) and then do 3 "Append to string" actions in a loop to append the next value to all variables and just add the variable in the new excel row. Problem with that approach is that in reality i'm having many lists with way more columns so i'm trying to find an expression that does the trick and avoid ending up with 200 initialize/append variable actions in a single flow.

2 ACCEPTED SOLUTIONS

Accepted Solutions
grantjenkins
Community Champion
Community Champion

This requirement aligns to using XPath perfectly.

 

My list used in this example:

grantjenkins_1-1669721897921.png

 

And my Excel Table:

grantjenkins_2-1669721946597.png

 

See my full flow below. I'll go into each of the actions.

grantjenkins_0-1669721844262.png

 

Get items retrieves all the items from my SharePoint List.

grantjenkins_3-1669721993765.png

 

XML is a Compose action that converts the body of Get items to XML so that we can use XPath in the next step. It also adds a root element to ensure its valid XML. The expression used here is:

xml(json(concat('{"root": ', outputs('Get_items')?['body'], '}')))

grantjenkins_4-1669722096272.png

 

And finally, we have our Add a row into a table which takes in the following expressions for Color, Day, and Pet respectively. This extracts out each of the values and joins them with a /

join(xpath(outputs('XML'), '//root/value/Color/text()'), '/')

join(xpath(outputs('XML'), '//root/value/Day/text()'), '/')

join(xpath(outputs('XML'), '//root/value/Pet/text()'), '/')

grantjenkins_6-1669722269188.png

 

And the output after the flow completes, we have the values joined, separated by /

grantjenkins_5-1669722231326.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

View solution in original post

You'd just need to wrap the array into a property as so:

 

xml(json(concat('{"root": { values:', body('Filter_array'), '}}')))


----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

View solution in original post

5 REPLIES 5
v-dezhili-msft
Microsoft
Microsoft

Hi @MuFeR ,

I have a test for your reference.

 

vdezhilimsft_0-1669714599999.png

 

vdezhilimsft_1-1669714600001.png

 

vdezhilimsft_2-1669714600002.png

 

vdezhilimsft_3-1669714600003.png

 

vdezhilimsft_4-1669714600005.png

 

vdezhilimsft_5-1669714600006.png

 

Best Regards,

Dezhi

grantjenkins
Community Champion
Community Champion

This requirement aligns to using XPath perfectly.

 

My list used in this example:

grantjenkins_1-1669721897921.png

 

And my Excel Table:

grantjenkins_2-1669721946597.png

 

See my full flow below. I'll go into each of the actions.

grantjenkins_0-1669721844262.png

 

Get items retrieves all the items from my SharePoint List.

grantjenkins_3-1669721993765.png

 

XML is a Compose action that converts the body of Get items to XML so that we can use XPath in the next step. It also adds a root element to ensure its valid XML. The expression used here is:

xml(json(concat('{"root": ', outputs('Get_items')?['body'], '}')))

grantjenkins_4-1669722096272.png

 

And finally, we have our Add a row into a table which takes in the following expressions for Color, Day, and Pet respectively. This extracts out each of the values and joins them with a /

join(xpath(outputs('XML'), '//root/value/Color/text()'), '/')

join(xpath(outputs('XML'), '//root/value/Day/text()'), '/')

join(xpath(outputs('XML'), '//root/value/Pet/text()'), '/')

grantjenkins_6-1669722269188.png

 

And the output after the flow completes, we have the values joined, separated by /

grantjenkins_5-1669722231326.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

Does the xml expression only work with the Get_items action or could it also be modified to work with Filter Array?

I tried to use
xml(json(concat('{"root": ', body('Filter_array'), '}')))

but i get the error "Unable to process template language expressions in action 'XML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'This document already has a 'DocumentElement' node.'."

You'd just need to wrap the array into a property as so:

 

xml(json(concat('{"root": { values:', body('Filter_array'), '}}')))


----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

Thanks this worked perfectly!
I also had to modify the next part from:
'//root/value/Color/text()' to '//root/values/Color/text()'  

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,705)