Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Outlook
    • Planner
    • Power Apps
    • SharePoint
    • Teams
  • Triggers
  • Ready to use solutions
    • Approval Process Solution
    • Task Delegation App
    • The Ultimate Power Automate expressions cheat sheet
    • Power Automate HTTP requests to SharePoint cheat sheet
    • Power Automate HTTP requests to Graph API cheat sheet
  • ABOUT ME
  • Get help with your flow
  • POWER PLATFORM SERVICES
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.


🚀 Master Power Automate

Join 2,100+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

30 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
        1. Jamie says:
          October 13, 2022 at 5:58 pm

          I am getting this same error and not understanding the issue.
          My column name is “Barrier to discharge; pending”
          So my expression is
          join(xpath(xml(json(concat(‘{“body”:{“value”:’, item()?[‘Barrier to discharge; pending’] , ‘}}’))), ‘/body/value/Value/text()’), ‘, ‘)
          I am using the SELECT action, then adding this output into the table.

          Reply
          1. Tom says:
            October 23, 2022 at 4:15 pm

            Hello Jamie,
            you must use the column internal name, not the display name, it might be something like Barier_x0020_to_x00… as explained here: https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/

  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
  8. Lou says:
    August 4, 2022 at 3:37 pm

    Thank you so much.

    This site is bookmarked with enthusiasm.

    Reply
  9. Osama says:
    October 5, 2022 at 11:59 am

    Hi Tom,
    Thank you for posting such problem and the solution,
    What if I am using a person field? a column name Action By and want to show the Display name

    join(xpath(xml(json(concat(‘{“body”:{“value”:’, item()?[‘Action By’] , ‘}}’))), ‘/body/value/Value/text()’), ‘, ‘)

    I tried it but it gave an error? Can you advise if this apply only on the multiple choice and as well person field?

    Reply
    1. Tom says:
      October 14, 2022 at 10:19 am

      Hello Osama,
      you must use the column internal name (https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/), not the display name.

      Reply
  10. Rakesh says:
    December 18, 2022 at 2:27 am

    Hi Tom,

    Is there a way to do it’s reverse, such as based upon CSV value update choice column respectively?

    Reply
    1. Tom says:
      January 9, 2023 at 3:41 pm

      Hello Rakesh,
      I probably don’t understand the question, if you have csv with values then you can use it to update items using the standard actions.

      Reply
  11. Alex says:
    December 20, 2022 at 3:18 pm

    Hi, thanks a lot!
    What if I need a only unique values from multiple choice column for set of records in the SharePoin list? Say, I have 2 records in the list. First once contains “Choice 1″ value in multiple choice column and the second contains “Choice 1″ and “Choice 2”. Using your method I will get two values in my CSV:
    Choice 1
    “Choice 1, Choice 2″
    But how do I get the following?:
    Choice 1
    Choice 2

    Reply
    1. Tom says:
      January 9, 2023 at 3:54 pm

      Hello Alex,
      I guess you’d need some pre/post processing of the values, but doing all of it seems terribly completed within a single expression inside the ‘Create csv table’ action.

      Reply
  12. Gergely Csata says:
    December 23, 2022 at 4:36 pm

    Hi Tom,

    I checked my Column name, mine is Countries, simple is that. I checked it in the Get items raw output. I use your expression:

    join(xpath(xml(json(concat(‘{“body”:{“value”:’,item()?[‘Countries’],’}}’))),’/body/value/Value/text()’),’,’)

    But I still get the body:value error seemingly I am not grabbing the correct column and this is driving me mad. What else can be wrong?? I am trying it with a simple compose and I fail at the concat step.

    concat(‘{“body”:{“value”:’,item()?[‘Countries’],’}}’)

    Inputs:
    {“body”:{“value”:}}

    Outputs:
    {“body”:{“value”:}}

    I triple checked, I am using the machine name of the column. Column has data, it’s multiple choice and I set data in the list. Get items works well, I can get the other data.

    Reply
    1. Tom says:
      January 9, 2023 at 4:05 pm

      Hello Gergely,
      the first thought from reading your comment is: do you reference the right action in the input? If the output shows that it has property ‘Countries’ but it doesn’t return any value when referencing it then I’d check the input.

      Reply
      1. Yash says:
        November 6, 2023 at 1:46 pm

        Hi Tom, i too am facing the same issue and i checked with the input, i found when the column value is blank the flow does not runs and shows the ‘body:value error’. Can you please let me know any changes that can be done in your expression
        join(xpath(xml(json(concat(‘{“body”:{“value”:’, item()?[‘COLUMN NAME’] , ‘}}’))), ‘/body/value/Value/text()’), ‘, ‘)

        Reply
        1. Tom says:
          November 19, 2023 at 2:02 pm

          Hello Yash,
          I didn’t have any problems with the expression no matter whether the column was empty or not, so it’s hard for me to advise how to solve your problem.

          Reply
        2. NIL says:
          May 13, 2024 at 5:13 am

          your column name has to be exactly the same as your list header. You might wanna check on cap letters, any unintentional spaces after the word or smth..

          Reply
  13. Pam says:
    April 24, 2023 at 10:07 pm

    OMG! I agree with all the comments already posted. This is amazing! I have spent countless hours over the past two weeks trying to get a multi-choice field exported with the rest of my single value items in a SP list. I watched and read so many other “solutions” and none of them worked. Thank you so much for posting this. I cannot begin to tell you what relief it is to finally have a working solution.

    Reply
  14. Sumaila says:
    June 21, 2023 at 2:22 pm

    Thanks this saved me so much time. I’m super grateful! Sending you thanks from Ghana!

    Reply
  15. Jodi says:
    April 22, 2024 at 3:28 pm

    Thank you so much for this post! I was trying so many things and getting so frustrated, but this solved it for me!

    Reply
  16. NIL says:
    May 13, 2024 at 5:11 am

    TYVM REALLY HELPED ME A LOT !!

    Reply
  17. Lolo says:
    June 11, 2024 at 11:03 pm

    Hello,
    Thank you for the post. I added this expression for the column I have that are “choice type”. I have a total of 19 fields and 8 are “choice” when I run the flow it runs successfully but when I open the excel sheet it only show 11 of the fields and only 1 of those 11 fields is of “choice” type for some reason I can’t see the rest of the fields in the excel although I have it map on the “create csv table” step.

    Reply
  18. Arjun says:
    September 17, 2024 at 8:38 am

    Thank you very much, this post helped me to resolve long-standing issue.

    Reply

Leave a Reply Cancel reply

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

🚀 Master Power Automate

Join 2,100+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

Still exchanging emails to get things approved?

Get the Approval Process solution and the Task Delegation App to skip the hard part and deploy an automated, fully functional approval solution on a SharePoint list in minutes! And then the next one, and the next one...

Approval Template Preview ✨ Learn more ✨

Turn ideas into flows with ease!

Grab the complete Power Automate Cheat Sheet Bundle—everything you need to master expressions, SharePoint HTTP calls, and Graph API in Power Automate.

Cheat Sheet Bundle Preview ✨ Get the Cheat Sheets

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with thousands of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2025 Let's POWER Automate | Theme by SuperbThemes