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

How to create a table using multi-select column for every rows/items from SharePoint Online List?

Hello, 

 

I am having so much trouble creating a Automate Flow to run weekly to check a SharePoint Online List and show new items in a table format. The List has a Multi-Select column (Choice). 

 

So far using Compose and Select, I was able to drill down data to get the following but is there a function I can use to get only the values then concatenate them? Using below data set, it would be like "Love, Care"

 

[
  {
    "Category""Love"
  },
  {
    "Category""Care"
  }
]

 

Please help

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @whatisgoingon 

Please try the following formula:

join(xpath(xml(json(concat('{"body":{"value":', item()?['Category'] , '}}'))), '/body/value/Value/text()'), ', ')
This is my test list:
vLilyWmsft_0-1646960874817.png

Flow in details:

vLilyWmsft_1-1646960910076.png

 

vLilyWmsft_2-1646960925698.png

After flow runs, the items will be create in excel:

vLilyWmsft_3-1646960965681.png

Hope the content above may help you.

Best Regards

If my reply helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
whatisgoingon
Frequent Visitor

Given that multi-select column is named 'test_multi_column', everytime i select 'test_multi_column value', it just keeps looping into 'Apply to Each' 

Hi @whatisgoingon 

Please try the following formula:

join(xpath(xml(json(concat('{"body":{"value":', item()?['Category'] , '}}'))), '/body/value/Value/text()'), ', ')
This is my test list:
vLilyWmsft_0-1646960874817.png

Flow in details:

vLilyWmsft_1-1646960910076.png

 

vLilyWmsft_2-1646960925698.png

After flow runs, the items will be create in excel:

vLilyWmsft_3-1646960965681.png

Hope the content above may help you.

Best Regards

If my reply helps, then please consider Accept it as the solution to help the other members find it more quickly.

abcrandy
Helper I
Helper I

@v-LilyW-msft I'm getting the error 'The template language function 'json' parameter is not valid. The provided value '{"body":{"value":}}' cannot be parsed: 'Unexpected character encountered while parsing value: }. Path 'body.value', line 1, position 17.' when trying to use the code provided. 

 

My multi select column is called Other_Benefit_Type and the code that I used is 

join(xpath(xml(json(concat('{"body":{"value":', item()?['Other_Benefit_Type'] , '}}'))), '/body/value/Value/text()'), ', ') .
 
One other thing to note is that I'm using the Insert row (v2) function instead of adding it to excel like the example previously provided.
abcrandy_0-1672362876723.png


Any advice would be greatly appreciated.

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!

Users online (5,315)