web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Export to Excel or CSV...
Power Automate
Unanswered

Export to Excel or CSV - SharePoint choice column

(0) ShareShare
ReportReport
Posted on by

I have a SharePoint list that contains a choice column. When I export the SharePoint list to excel, the choice column appears like this:
Red;#Blue;#Green;#White. Instead, I would like it to appear as Red, Blue, Green, White. Therefore, I thought creating a flow will allow me that opportunity, however I haven't found a solution that will work.

I tried using the "select output" step to map the SharePoint columns, but I do not get the column "value" to map, only the column name.
Any ideas?

Categories:
I have the same question (0)
  • v-xiaochen-msft Profile Picture
    on at

    Hi @AnnetteM ,

     

    I made a sample for you.

    vxiaochenmsft_0-1670382903089.png

    vxiaochenmsft_1-1670382924733.png

    vxiaochenmsft_2-1670382964756.png

    vxiaochenmsft_3-1670383033581.png

    items('Apply_to_each')?['myChoice']
    item()?['Value']
    vxiaochenmsft_4-1670383073676.png

    {"Title":@{items('Apply_to_each')?['Title']},"myChoice":@{join(body('Select_2'),',')}}

     

    vxiaochenmsft_5-1670383105966.png

     

    Result:

    vxiaochenmsft_6-1670383124244.png

     

    Best Regards,

    Wearsky

  • Verified answer
    grantjenkins Profile Picture
    11,063 Moderator on at

    I would convert your JSON data to XML then use XPath to get the list of values from your choice field.

     

    For this example, I'm using the following list and will extract out the Title (renamed as Team Name) and Countries (multi-select choice field).

    grantjenkins_0-1670422315092.png

     

    grantjenkins_1-1670422339183.png

     

    Get items returns the items from my list.

    grantjenkins_2-1670423554698.png

     

    XML is a Compose that converts our data to XML so we can use XPath expressions. Note that it also adds a root element to the data to ensure it's valid XML. The expression used is:

    xml(json(concat('{"root": ', outputs('Get_items')?['body'], '}')))

    grantjenkins_3-1670423715329.png

     

    Select uses the output from XML and a couple of XPath expressions to get the Team Name (Title field) and the list of Countries.

     

    The input (From) uses the following expression which will give us the list of list items.

    xpath(outputs('XML'), '//root/value')

     

    The expression to get the Team Name (Title field) is:

    xpath(item(), 'string(//Title/text())')

     

    The expression to get the list of Countries, joined by a comma is:

    join(xpath(item(), '//Countries/Value/text()'), ', ')

     

    The Select looks like that below, with the above expressions.

    grantjenkins_4-1670423744387.png

     

    This will give us output we can use in a Create CSV table, Create HTML table, attach to an email, or save to SharePoint, etc.

     

    In this example, I'm using the output to create a CSV table.

    grantjenkins_5-1670424040083.png

     

    And attaching the CSV table to an email.

    grantjenkins_6-1670424093438.png

     

    And the CSV table data:

    grantjenkins_7-1670424241083.png

     

  • AnnetteM Profile Picture
    on at

    This was perfect! One stumbling block with the SELECT statement with the XPath on a string/text (single line of text SharePoint column) field that has a value of "5 - 8", when written to the .csv, it formats the content as 8-May. Is there a way to take the characters as literal and not convert to a date?

  • AnnetteM Profile Picture
    on at

    I figured it out! In order to avoid .csv trying to convert a string of 5 - 8 to 8-May, I added a tab string to the value mapping.

    I created a variable called charTab as a string and the value of a "tab" was copied in. To get the tab representation, I accessed Notepad, clicked the tab key, then did a copy/paste in the value of the variable. Then placed the variable before the XPath expression.

    AnnetteM_0-1670446766028.png

     

  • Rat2 Profile Picture
    on at

    Hi @grantjenkins 
    Thank you for the solution.
    However is that possible to split column B to different columns using power automate

  • grantjenkins Profile Picture
    11,063 Moderator on at

    @Rat2 I'd suggest posting your question and putting as much info as you can. Feel free to ping me when you create the new post.

  • PAuser_1-2-3 Profile Picture
    52 on at

    @grantjenkins The XML conversion seems to work but how can we combine additional non choice columns to the Select action? Can the outputs also be used to create Excel file, specifically .xlsx file?

  • grantjenkins Profile Picture
    11,063 Moderator on at

    Hi - I'd suggest creating a new post with a reference to this post and what you're after, and at mention me in there so I see it.

  • PAuser_1-2-3 Profile Picture
    52 on at

    @grantjenkins there are already many similar posts in the community.  To help other users find the right answers quick, I think it's better to keep all communications in the same post, especially this thread has a good relevant title.  

  • grantjenkins Profile Picture
    11,063 Moderator on at

    @PAuser_1-2-3 This question (and Title) was about Choice fields, but you are asking about non-Choice fields. I'm not actually sure what you mean by non-Choice fields. Did you mean multi-select Person, Metadata, etc.? or something else? That's why I mentioned a separate post as it's asking for something different to the original post.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 522 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 364 Moderator

#3
abm abm Profile Picture

abm abm 243 Most Valuable Professional

Last 30 days Overall leaderboard