Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Outlook
    • Planner
    • Power Apps
    • SharePoint
    • Teams
  • Triggers
  • Ready to use solutions
    • Approval Process Template
    • Task Delegation App
    • The Ultimate Power Automate expressions cheat sheet
    • Power Automate HTTP requests to SharePoint cheat sheet
    • Power Automate HTTP requests to Graph API cheat sheet
  • ABOUT ME
  • Get help with your flow
  • POWER PLATFORM SERVICES
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.


πŸš€ Master Power Automate

Join 2,000+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

27 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. Becky says:
      February 8, 2024 at 2:32 pm

      Thank you for this. Took me a few tries, but I got it!

      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
  11. Fred says:
    April 6, 2023 at 2:31 am

    Tom, you are a LIFESAVER. I have been trying for MONTHS to figure out how to combine both multi choice select fields and multi select person fields and finally got the solutions from these two articles.

    I have one step further that I’m wondering you can help with.

    I have a multi-select person field that is named ‘AnalystAssigned’. Instead of combining display fields, I need to use each analyst’s ‘Given Name’ and ‘Surname’ property from their user profile.

    I currently do this by appending the Given and Surname to a string variable, but I want to be able to have a scenario where when I have more than one analyst, the Given Name Surname combinations are separated by ‘and’ — if I just put ‘and’ at the end of my string variable, it adds the ‘and’ even if there is only one analyst assigned so we have to manually delete that.

    I’ve tried mapping a ‘Select’ and ‘Join’ inside an apply to each that pulls from the ‘AnalystAssigned’ main field, but can’t extract the results. Where am I going wrong?

    Again, thank you so much for all your articles. You have got me across the finish line countless times!!!

    Reply
    1. Tom says:
      April 20, 2023 at 9:53 pm

      Hello Fred,
      I’d expect that the ‘Given Name’ + ‘Surname’ = the ‘DisplayName’ property. You should be able to get their names with a small adjustment in the xpath(…) expression from the article.
      join(xpath(xml(json(concat('{"body":{"value":', item()?['AnalystAssigned'] , '}}'))), '/body/value/DisplayName/text()'), ' and ')

      Reply
      1. Fred says:
        May 13, 2023 at 2:58 am

        Thanks, Tom. Unfortunately, my enterprise’s display names also include an abbreviation for our offices (so mine spits out LastName, Fred, HQ). When I want to have it just kick out Fred LastName and if you and I were both in the people picker it would kick
        Fred LastName and Tom Riha

        Been struggling with this since my users don’t take the extra 10 seconds to make the outputs more grammatically correct and so we send something out that says
        POCs: Fred LastName Tom Riha
        instead of
        POCs: Fred LastName and Tom Riha

        Reply
        1. Tom says:
          May 28, 2023 at 3:17 pm

          Hello Fred,
          right now the only idea I have is to use replace(…) on the output to replace all the offices with ”, but I’m not sure if that’s doable depending how many offices you have. I can’t think of any simple solution that I could describe here.

          Reply
  12. Ramisa says:
    April 24, 2023 at 8:17 pm

    Any idea how to make the headers in the excel bold when exporting from sharepoint list to ecel?

    Reply
    1. Tom says:
      May 7, 2023 at 2:24 pm

      Hello Ramisa,
      not with this approach as it’s creating a .csv file – text file, no formatting.

      Reply
  13. Chris says:
    June 7, 2023 at 9:48 pm

    AMAZING!

    This approach keeps the number of Power Automate steps to a minimum while resulting in unimaginable speed improvements when compared to using an Apply to Each step with other steps such as Append to String: 5 seconds versus 3 minutes!

    I’m also using this approach for multiple choice and lookup columns.

    THANK YOU!

    Reply
  14. mr says:
    July 28, 2023 at 2:24 pm

    Wait there’s a way to convert JSON to array I believe. Using this site here:
    https://www.journeyteam.com/post/do-i-need-the-parse-json-action-in-power-automate#:~:text=The%20Parse%20JSON%20action%20in%20Power%20Automate%20is%20a%20powerful,dynamic%20content%20for%20your%20flow.

    ForAll( ParseJSON( JsonString ).array, Value( ThisRecord ) )
    ForAll( ParseJSON( JsonString ).array, { id: Value(ThisRecord.id), name: Text(ThisRecord.name) })

    I will test today and see but if anyone is able to figure it out before me let us know!

    Reply
  15. AZ says:
    July 14, 2024 at 7:02 pm

    I’m trying to do the same for my multiple choice column called department (‘field_23’ when i copy pasted it) but its to working
    I have earlier for my multiple person column and it worked perfectly
    any help?

    join(xpath(xml(json(concat(‘{“body”:{“value”:’, item()?[‘field_23’] , ‘}}’))), ‘/body/value/text()’), ‘, ‘)

    Reply
    1. Ruchika says:
      February 4, 2025 at 9:11 am

      Try This:

      join(xpath(xml(json(concat(‘{“root”:{“choices”:’, item()?[‘field_23’] , ‘}}’))), ‘/root/choices/Value/text()’),’ , ‘)

      Reply
  16. Danielx64 says:
    March 3, 2025 at 3:52 am

    Hi

    What’s the proper syntax for using this right after a SharePoint Get item (I want to get a single SharePoint item)? I see that this can be used in a create csv function (and I have used it there before) but i’m wanting to get the email addresses out of a SharePoint column and then format it to go into the “To:” line in an email.

    Cheers

    Reply
    1. Tom says:
      March 11, 2025 at 7:06 pm

      Hello Daniel,
      if you have just one SP item you don’t need this complex expression, you can just ‘Select’ the email addresses from the people picker field and join(…) them by semicolon: https://tomriha.com/how-to-select-specific-values-from-an-array-in-power-automate/

      Reply
  17. Lucian says:
    May 2, 2025 at 6:11 pm

    THANK YOU, I spent the whole day yesterday trying to figure out how to do exactly this and finally found your article today, I’m bookmarking this site for future issues.

    Reply

Leave a Reply Cancel reply

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

πŸš€ Master Power Automate

Join 2,000+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

Working on an Approval process?

Use the Approval Process Template and the Task Delegation App to skip the hard part and deploy a fully functional approval solution on a SharePoint list in minutes! And then the next one, and the next one...

Approval Template Preview ✨ Learn more ✨

Turn ideas into flows with ease!

Grab the complete Power Automate Cheat Sheet Bundleβ€”everything you need to master expressions, SharePoint HTTP calls, and Graph API in Power Automate.

Cheat Sheet Bundle Preview ✨ Get the Cheat Sheets

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with thousands of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2025 Let's POWER Automate | Theme by SuperbThemes