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

[whatisgoingon]

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

[whatisgoingon]

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' 

[v-LilyW-msft · Accepted solution]

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:

Flow in details:

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

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]

@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.

Any advice would be greatly appreciated.