Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

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

Posted on January 24, 2021November 21, 2021 by Tom

“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']
power automate csv table export person column

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.

power automate csv table export person column

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.

power automate csv table export person column json

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.

power automate csv table export person column xml

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.

Zero spam, unsubscribe anytime.

13 thoughts on “Export multiple Person or Group column into .csv table in Power Automate”

  1. Roo says:
    January 27, 2021 at 11:48 pm

    This is the way. Got it down to just the DisplayName of the claims, now just need to get rid of [“”] surrounding it. [“Mister Fiddlesticks”]

    Reply
    1. Tom says:
      January 28, 2021 at 11:58 am

      Hello Roo,
      to turn the result array into a more user friendly string you must remove/replace the array formatting [" and "," and "] using the expressions below:

      1. string(xpath(...)): the string(…) expression will convert the array into a string, the necessary input for replace(…) expression
      2. replace(string(xpath(...)),'["',''): replace the array opening characters [” with (nothing) in the string
      3. replace(replace(string(xpath(...)),'["',''),'","',', '): replace the separator between values “,” with , (comma and space)
      4. replace(replace(replace(string(xpath(...)),'["',''),'","',', '),'"]',''): replace the array closing characters “] with (nothing)

      If I take the full expression from the article it would look like this:
      replace(replace(replace(string(xpath(xml(json(concat('{"body":{"value":', item()?['SendTo'] , '}}'))), '/body/value/Email/text()')),'["',''),'","',', '),'"]','')

      Reply
  2. Roo says:
    January 28, 2021 at 6:11 pm

    Used a join to fix it. Seems to be working with blanks as well. Keep in mind I’m using DisplayName instead of Email.
    join(xpath(xml(json(concat(‘{“body”:{“value”:’, item()?[‘SendTo’] , ‘}}’))), ‘/body/value/DisplayName/text()’),’, ‘)

    Reply
    1. Tom says:
      January 28, 2021 at 6:29 pm

      That looks like a much better solution than the one I proposed, thank you for sharing.

      Reply
  3. Roo says:
    January 28, 2021 at 6:14 pm

    Okay yah I will put that in my toolbox too. Thanks

    Reply
  4. Ashwin Chinmulgund says:
    August 11, 2021 at 8:05 am

    Great! Just the solution I was looking for.

    Reply
  5. naveen says:
    May 15, 2022 at 11:46 am

    Hi I am creating excel file using power automate, here the flow working fine but the problem is in my list have people picker field so i am getting all the data but i need only name. can anyone help this issue.

    the data is like this :{“Claims”:”i:0#.f|membership|bhaaaavna.fafanchan@taann.com”,”Department”:”nav”,”DisplayName”:”navin”,”Email”:”bhaaaavna.fafanchan@taann.com”,”JobTitle”:null,”Picture”:”https://taanworkplace.sharepoint.com/sites/ExpenseClaims/_layouts/15/UserPhoto.aspx?Size=L&AccountName=bhaaaavna.fafanchan@taann.com”}

    Reply
    1. Tom says:
      May 17, 2022 at 8:35 am

      Hello Naveen,
      looking on the data you shared it doesn’t contain [ ], which makes me think that you don’t need the solution described in this article, the solution applies to multiple people picker fields. For single selection field just select the dynamic content.

      Reply
  6. Zach says:
    August 2, 2022 at 10:26 pm

    This is great. Thank you

    Reply
  7. Amy says:
    December 1, 2022 at 6:40 pm

    Thank you so much!! Struggling with this until I found your post.

    Reply
  8. Shaikh Ahemad says:
    January 3, 2023 at 12:49 pm

    Thanks! it helps a lot.

    Reply
  9. Damon Kerry says:
    February 28, 2023 at 12:35 pm

    Great solution, thank you. Worked like a charm

    Reply
  10. Jerome Rancourt says:
    March 27, 2023 at 3:57 pm

    Thank’s Tom, exactly what i was looking for

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

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.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes