Connecting SharePoint List To Excel Doesn't Return Person Column Data

[LuiIacobellis]

Hello,

I had a question regarding the behaviour I am observing when I connect to a SharePoint Online list from Excel. Specifically, in Excel, I click on the data tab > get data > from online services > from SharePoint Online list. I establish the connect to my site and am able to successfully retrieve data from the list. 

However, I am seeing that certain column types including person types and look up columns do not return the actual data stored in the column for each item but rather just return the value "[List]" for all items. I've attached a screenshot below that displays the list and what appears in Excel. I've also attached the screenshot for reference.

I haven't been able to identify any limitations with certain column types when trying to research this behaviour. If anyone has any insights or documentation that might explain this behaviour, that would be greatly appreciated. 

Thank you!

[#2]

@LuiIacobellis Probably because person or group and lookup columns are complex fields, not like simple text fields.

These fields have additional data associated with it like person columns has different properties like user display name, email, job title, etc.

Similarly, lookup columns has lookup list item ID and lookup value of primary column selected in column settings.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

Hi @LuiIacobellis, as Hi, as @ganeshsanap has said, this is a complex field type it will show this value. To get to the data underneath, you will need to use Power Query Editor and transform the data a little - here is some guidance on getting started with that: https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a... 

[#4]

Thank you!

[#5 · Accepted solution]

I've since figured out how to resolve this issue and have posted a tutorial outlining the steps:

https://youtu.be/dJbwDTMXt74