Sharepoint to Excel - Column allowing multiple selections

[Rob_Sims]

Hi there,

I'm exporting from a SharePoint list to Excel. The SharePoint list has a column that allows the user to select the weather condition. This column allows the user to select multiple answers e.g. Snow/Ice, Flood, Fog/Mist

When I export to Excel using Power Automate I get a string of data in a single cell:

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":3,"Value":"Snow/Ice"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":5,"Value":"Flood"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Fog/Mist"}]

Is there a way in Power Automate to make it only show the values from the SharePoint list when it exports to Excel?

Any help greatly appreciated 🙂

[Rhiassuring]

Hi there, you will need to build a string for each. For each line you add, have a txtChoices string, then, use an "Append to String" that is focused on the Value from your choice value. It will loop through each choice, and add it, for the item you're looking at. Then, just use the txtChoices as the input for your Excel row, rather than the SP data. Have a "set variable" at the beginning of your loop entry to clear it out. 

Let me know i fyou need a step by step.

[Rob_Sims]

Thank you that's great!

If you are able to provide a step-by-step it would be massively appreciated 🙂

[Rhiassuring · Accepted solution]

Hi there!

I hope this isn't confusing, but, I decided to try a new way of doing this that I read about on @tom_riha's blog, here. 

In short, instead of appending to a string (which is still a valid method!) we're going to use Select, and then Join()

In my test, I had a multi-select duck column.:

I use the "Select" action to point at the Ducks column, and then put the "Ducks Value" into the Mapping. 

It's important to note that little yellow highlighted bit needs to be the little index card looking thing, instead of the icon that has a T in it. If it isn't, click on it, and it'll swap.

Then, we just use a Join() expression inside of a Compose action.

All it's saying is "hey, look at this array. Let's smash them all into a string and put a character between them - how about a comma."

join(body('Select'),', ')

Then, you can cram the output of that Compose into whereever you need it to go!

Cheers,

Rhia

[Rob_Sims]

That's awesome Rhia, I can't thank you enough!!!

[santee]

Where exactly do you place the select & compose bits? As new step? between the get items and apply to each...or as nested within the apply to each?

[Rob_Sims]

I've placed mine after the get data (got 4 different ones).

I then have an update a row in excel, and have inserted the compose options in there.