Format multiple choice SharePoint column in .csv using Power Automate

“How can I format the values from multiple choice SharePoint column in a .csv file created by Power Automate? It shows an ugly string instead of the value(s).”

{“@odata.type”:”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”, “Id”:2, “Value”:”In progress”}


When working with multiple choice SharePoint columns in Power Automate, they’ll be always processed as an array. Until you run the flow, Power Automate doesn’t know what value a column contains, it knows only the list/library structure. And if the structure has a choice column with multiple choice enabled, it must be prepared to process all of them. To receive all of them in an array and process them one by one.

That’s why you don’t see the dynamic content for ‘Value’ of the multiple choice field (unlike single choice columns). Until you loop through all the choices in the array, Power Automate doesn’t know what values they contain. And since you can’t loop inside the ‘Create CSV table’ action, Power Automate will allow you to use only the full array. Even if it contains only a single choice for the given item.

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", "Id":2, "Value":"In progress"}
power automate format csv multiple choice

To access only the ‘Value’, you must filter it out from the array. And since you can’t loop, it must be done using a single expression.

Build the expression

I already went through the process of building the expression in THIS post. But if you want the expression right away, it should look as below.

join(xpath(xml(json(concat('{"body":{"value":', item()?['COLUMN NAME'] , '}}'))), '/body/value/Value/text()'), ', ')

The expression above will take the whole string {“@odata…} and convert it into a valid JSON. Valid JSON can be then converted into xml, and xpath(…) will allow you to access the desired values from the array. Join(…) will just format the final output into a comma separated string.

Example

The ‘MultipleChoice’ column from the previous screenshot contains the following string, 3 choices were selected:

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Choice 1"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Choice 2"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"Choice 3"}]

You want to extract only the values, a string “Choice 1, Choice 2, Choice3”. Instead of using the dynamic content directly as on the screenshot, use the expression with the ‘MultipleChoice’ column name.

join(xpath(xml(json(concat('{"body":{"value":', item()?['MultipleChoice'] , '}}'))), '/body/value/Value/text()'), ', ')
power automate format csv multiple choice

The MultipleChoice column in the .csv file will contain the desired value: “Choice 1, Choice 2, Choice 3”.

Summary

I went much deeper into the expression in THIS article. The approach is the same, the only difference is what value you want from the multiple choice column.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *