Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

Format multiple choice SharePoint column in .csv using Power Automate

Posted on February 3, 2021January 6, 2022 by Tom

“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 in the value you want from the multiple choice column. Otherwise it’s the same Power Automate xpath(…) expression, only this time you extract and format the multiple choice column into a csv.


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.

10 thoughts on “Format multiple choice SharePoint column in .csv using Power Automate”

  1. Ashwin Chinmulgund says:
    August 11, 2021 at 7:52 am

    Thank you so much for this!! This is very smart!

    Reply
  2. Neil M says:
    September 15, 2021 at 3:26 pm

    I owe you a cyber pint. I struggled with this for a while. I fixed it in 5 mins after reading your post. I have no idea how you figured this out but I am glad you did. I tip my cap to you sir.

    Reply
  3. Adam Kurd says:
    February 7, 2022 at 5:00 pm

    Thank you so much, it worked for me too! you are really life saver.

    Reply
  4. Claudette says:
    April 27, 2022 at 8:28 pm

    I tried your solution, but what if the multiple choice column has a null value, how does this affect your solution?

    Reply
    1. Tom says:
      May 3, 2022 at 7:59 pm

      Hello Claudette,
      if there’s no value then the xpath() won’t find anything = it’ll return an empty array = the outcome will be an empty value.

      Reply
  5. Roman says:
    May 17, 2022 at 5:29 pm

    You BEST !!!

    Reply
  6. Sarah R says:
    May 25, 2022 at 10:10 am

    Hi – however hard I try I get an error when trying to run this code:

    The execution of template action ‘Peripherals_CSV’ failed. The column values could not be evaluated:
    ‘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.’. Please see https://aka.ms/logicexpressions#json for usage details.’.

    Can you guide me as to where I might be going wrong?

    Reply
    1. Sarah R says:
      May 26, 2022 at 2:32 pm

      Resolved it – I’m new to this and didn’t know I need to precede this with Get Items actions. It works brilliantly now!

      Reply
      1. Tom says:
        June 1, 2022 at 5:32 pm

        Hello Sarah,
        I’m glad that you solved it. You can read from the error message that the parameters of the ‘json’ functions were not valid as it contained only ‘{“body”:{“value”:}}’ = it was completely missing the data from the SP column.

        Reply
  7. Craig Marshall says:
    May 27, 2022 at 1:27 am

    Jesus wept man…. I spent hours faffing with this and was about to give up before finding your brilliant blog and resolving things instantly! Thank you so much for sharing. Kudos.

    Prior to this I had a second flow set up just to populate a slave text column, triggered on ‘item created or modified’ – which then resulted in countless more hours faffing about to prevent the infinite triggering risk!

    Reply

Leave a Reply Cancel reply

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

Now available:

The Ultimate Power Automate expressions cheat sheet
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

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.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme