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?
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())'))
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:
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())'))
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())')
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()'), ', ')
@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_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.
@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.
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.
@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?