Export to Excel or CSV - SharePoint choice column

[AnnetteM]

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?

[v-xiaochen-msft]

Hi @AnnetteM ,

I made a sample for you.

items('Apply_to_each')?['myChoice']

item()?['Value']

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

Result:

Best Regards,

Wearsky

[grantjenkins · Accepted solution]

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

Get items returns the items from my list.

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'], '}')))

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.

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.

And attaching the CSV table to an email.

And the CSV table data:

[AnnetteM]

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]

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.

[Rat2]

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

[grantjenkins]

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

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

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]

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

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

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

[grantjenkins]

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]

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]

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]

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:

[grantjenkins]

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())'))