Skip to main content

Notifications

Power Automate - Building Flows
Answered

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?

  • grantjenkins Profile Picture
    grantjenkins 11,057 on at
    Re: Export to Excel or CSV - SharePoint choice column

    What if you default it to zero if not a number.

     

    if(equals(xpath(item(), 'string(number(//Field/text()))'), 'NaN'), 0, xpath(item(), 'number(//Field/text())'))
  • PAuser_1-2-3 Profile Picture
    PAuser_1-2-3 52 on at
    Re: Export to Excel or CSV - SharePoint choice column

    This IF expression works but its output does not work with the sort expression. It fails even if I enter '0' value or leave blank as ''. Here is the error message:

     

    PAuser_123_0-1701273372850.png

     

     

     

  • grantjenkins Profile Picture
    grantjenkins 11,057 on at
    Re: Export to Excel or CSV - SharePoint choice column

    I spent a bit of time trying to find a nice solution to the NaN issue, but given Power Automate only supports the XPath specification 1.0, there aren't a lot of options. This is one way around it where you can specify what you want in place of the NaN (could replace with string or maybe 0 depending on what you want to do).

    if(equals(xpath(item(), 'string(number(//Field/text()))'), 'NaN'), 'VALUE YOU WANT IF NOT A NUMBER', xpath(item(), 'number(//Field/text())'))

     

  • PAuser_1-2-3 Profile Picture
    PAuser_1-2-3 52 on at
    Re: Export to Excel or CSV - SharePoint choice column

    Thanks for the detailed response. I tested a few and they worked well except when numbers are null, the result would show "NaN".  How can we modify the following expression to show blank for null values?

     

    xpath(item(), 'number(//Field/text())')

     

  • grantjenkins Profile Picture
    grantjenkins 11,057 on at
    Re: Export to Excel or CSV - SharePoint choice column

    Below are some samples of how you would extract out data using xpath.

     

    //Single line of text
    xpath(item(), 'string(//Field/text())')
    
    //Multiple lines of text
    xpath(item(), 'string(//Field/text())')
    
    //Choice (Single select)
    xpath(item(), 'string(//Field/Value/text())')
    
    //Choice (Multi select)
    join(xpath(item(), '//Field/Value/text()'), ', ')
    
    //Number
    xpath(item(), 'number(//Field/text())')
    
    //Currency
    xpath(item(), 'number(//Field/text())')
    
    //Date and time
    xpath(item(), 'string(//Field/text())')
    
    //Lookup
    xpath(item(), 'string(//Field/Value/text())')
    
    //Yes/No
    xpath(item(), 'string(//Field/text())')
    
    //Person (Single select)
    xpath(item(), 'string(//Field/DisplayName/text())')
    xpath(item(), 'string(//Field/Email/text())')
    xpath(item(), 'string(//Field/Department/text())')
    xpath(item(), 'string(//Field/JobTitle/text())')
    xpath(item(), 'string(//Field/Claims/text())')
    xpath(item(), 'string(//Field/Picture/text())')
    
    //Person (Multi select)
    join(xpath(item(), '//Field/DisplayName/text()'), ', ')
    join(xpath(item(), '//Field/Email/text()'), ', ')
    join(xpath(item(), '//Field/Department/text()'), ', ')
    join(xpath(item(), '//Field/JobTitle/text()'), ', ')
    join(xpath(item(), '//Field/Claims/text()'), ', ')
    join(xpath(item(), '//Field/Picture/text()'), ', ')
    
    //Managed Metadata (Single select)
    xpath(item(), 'string(//Field/Label/text())')
    xpath(item(), 'string(//Field/TermGuid/text())')
    
    //Managed Metadata (Multi select)
    join(xpath(item(), '//Field/Label/text()'), ', ')
    join(xpath(item(), '//Field/TermGuid/text()'), ', ')
    
  • PAuser_1-2-3 Profile Picture
    PAuser_1-2-3 52 on at
    Re: Export to Excel or CSV - SharePoint choice column

    @grantjenkins the question was related to your XML conversion method. I tried this XPath expression and it worked with text, number and calculated fields but how can it work with other fields in SharePoint like currency, date time, person, lookup, etc?  I prefer to have all expressions in one Select action instead of adding additional actions to the flow. 

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

     

    These are other columns in SharePoint:

    PAuser_123_0-1700494986993.png

     

  • grantjenkins Profile Picture
    grantjenkins 11,057 on at
    Re: Export to Excel or CSV - SharePoint choice column

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

  • PAuser_1-2-3 Profile Picture
    PAuser_1-2-3 52 on at
    Re: Export to Excel or CSV - SharePoint choice column

    @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
    grantjenkins 11,057 on at
    Re: Export to Excel or CSV - SharePoint choice column

    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
    PAuser_1-2-3 52 on at
    Re: Export to Excel or CSV - SharePoint choice column

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

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

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard