Export multiple Person or Group column into .csv table in Power Automate

“I need only the user name from the Person or Group SharePoint column that Power Automate just exported into a csv table, not the full user data.”

---

When you export SharePoint list to a .csv, most of the columns will be exported as expected. But some of them won’t, for example the Person or Group column with multiple selection enabled. It works fine if you allow only a single user/group, then you can select which user property you want to export. But with multiple selection it’s not possible. You’ll get a similar string as shown below:

[{""@odata.type"":""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser"",""Claims"":""i:0#.f|membership|user@company.com"",""DisplayName"":""Name Surname"",""Email"":""user@company.com"",...}, {""@odata.type"":""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser""...}]

So, how do you get only a specific property for all users in the field, e.g. email or display name?

Build an expression

As you can’t preprocess the data before the ‘Create CSV table’ action, you must use a single (not simple) expression that will do it all. Expression, that will take the whole array with multiple users and their properties, and return just selected property per user.

Start by adding the dynamic content into the ‘Create CSV table’ action. Then copy/paste it into a text editor. That’s the dynamic content with the whole array with all the users and their properties. For multiple Person or Group column called ‘SendTo’ it’ll look as below:

@item()?['SendTo']

And remove the @ at the beginning, it’s not needed.

item()?['SendTo']

But it’s not possible to parse a specific property from the array directly. First, you need an expression that’s able to do that. Among the expressions in Power Automate is an expression xpath(…) that can be used, if you pass it the right parameters.

xpath(…) expression

Xpath(…) expression expects two parameters, first of them is an xml, the second is a path to the value in that xml. As a result you’ll get an array with the values on the defined path. As you don’t have an xml at this time, you must create one with the xml(…) expression.

xpath(xml(...), '[xmlPath]')

xml(…) expression

Xml(…) expression will convert a string in xml format to an actual xml. But the array with users is not an xml as Power Automate formats the data in JSON. Luckily, xml(…) expression can covert also JSON into xml, if you provide a valid JSON.

xpath(xml(json(...)), '[xmlPath]')

json(…) expression

That’s the next step, build a valid JSON with the users array inside. If you check a JSON produced by Power Automate, e.g. by the ‘Get items’ action, it has format as on the screenshot below.

The part after “value”: has the same format as Power Automate gives you for the multiple Person or Group column (or any other array). And that’s also what you can place there.

[{""@odata.type"":""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser""...}, {""@odata.type"":""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser""...}]

Build the JSON by enclosing the user data in the “body” and “value” elements using the concat(…) expression.

xpath(xml(json(concat('{"body":{"value":', item()?['SendTo'], '}}'))), '[xmlPath]')

Note:
item()?['SendTo'] is the dynamic content representing the Person or Group column. You copy/pasted it into a text editor at the beginning.

The result of the json(concat(…)) expression will be a JSON similar to the one below.

back to xpath(…) expression

Now you’ve got the JSON to convert to the xml to use in the xpath expression. The last part is to define the [xmlPath] parameter: the elements path to the value you want. Below is an example of an xml created by the xml(json(concat(…) expressions.

To get the email value, you go in the “body” element, then the “value” element, and the “Email” element. Once you’re in the Email element, you want to display its value as a text using a text() function.

body/value/Email/text()

At this moment you can put the whole expression together. This expression will return only email addresses of all users stored in the ‘SendTo’ column.

xpath(xml(json(concat('{"body":{"value":', item()?['SendTo'] , '}}'))), '/body/value/Email/text()')

join(…) expression

As mentioned in the comments by Roo, you might want to format the result as a string instead of an array. The simplest way is to use join(…) expression. Use the whole xpath(…) expression above as the first parameter and a separator, e.g. ‘, ‘ (comma + space) as the second parameter.

join(xpath(xml(json(concat('{"body":{"value":', item()?['SendTo'] , '}}'))), '/body/value/Email/text()'), ', ')

The result

At the end, the data provided by the expression will be just the email addresses separated by a comma.

user@company.com, user2@company.com

Summary

If you use only simple column types, exporting SharePoint lists into CSV table is a simple task with the ‘Create CSV table’ action. But if you add a multiple choice column, it gets more interesting.

In this article you could learn how to get email addresses of multiple users, but it’s not limited only to emails. By changing the [xmlPath] you can take a different value, e.g. display name or department.

It’s also not limited to a Person or Group column. You can follow the same process to access any other column with multiple options, e.g. choice column.

And if you’re interested to learn more about the use of xml and xpath in Power Automate, you can check also the Microsoft article on this topic.

---

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: