Sharepoint to Excel - Column allowing multiple selections

Like (0) Share

Report

Posted on 11 May 2022 16:01:05 by 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 🙂

I have the same question (0)

All responses (6)

Answers (1)

Rhiassuring 8,690 Moderator on 11 May 2022 at 19:20:17

Like (2)

Report

Re: Sharepoint to Excel - Column allowing multiple selections

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.

Was this reply helpful? Yes No
Rob_Sims 12 on 12 May 2022 at 08:14:35

Like (0)

Report

Re: Sharepoint to Excel - Column allowing multiple selections

Thank you that's great!
If you are able to provide a step-by-step it would be massively appreciated 🙂

Was this reply helpful? Yes No
Verified answer

Rhiassuring 8,690 Moderator on 13 May 2022 at 00:03:24

Like (2)

Report

Re: Sharepoint to Excel - Column allowing multiple selections

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

Was this reply helpful? Yes No
Rob_Sims 12 on 16 May 2022 at 08:06:46

Like (1)

Report

Re: Sharepoint to Excel - Column allowing multiple selections

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

Was this reply helpful? Yes No
Community Power Pla... on 22 Jun 2022 at 08:00:51

Like (1)

Report

Re: Sharepoint to Excel - Column allowing multiple selections

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?

Was this reply helpful? Yes No
Rob_Sims 12 on 22 Jun 2022 at 08:37:15

Like (0)

Report

Re: Sharepoint to Excel - Column allowing multiple selections

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.

Was this reply helpful? Yes No