Power Query unable to retrieve table from a SharePoint person column

[allan_t]

Encountered a new issue I'm not sure how to resolve or work around.   Power BI query is connected to a SharePoint list.  The list contains about 5000 items.  A column in this SharePoint list is called LeadAssigned, it's a Person column that allows multiple selections.   

In Power Query, I want to expand the LeadAssigned column so I can include the email address of the LeadAssigned in my final output table. 

The LeadAssigned column shows with a red bar indicating an error, and displays the following error message.  I've not encountered this before.  When I've used this method in Excel Power Query, I'm able to expand the column, but in Power BI Power Query it's not working.   

Any advice on how to resolve or work around this?   

[christinepayton · Accepted solution]

Are you using the 1.0 connector? There is a bug with 1.0 where occasionally it won't let you expand person type columns (seemingly at random, though I'm sure there's a reason). I would try using 2.0 list connector, or changing the API version in the advanced query editor from 15 to 14. Be aware that both of these will change the data structure and you'll end up with different column names, so you will have to go back and update or redo any query steps that referenced the old schema. 

[Anonymous]

Hi @allan_t 

As you said "When I've used this method in Excel Power Query, I'm able to expand the column, but in Power BI Power Query it's not working. " Do you mean at the same time, it does not work in Power BI's Power Query, but it works in Excel's Power Query? Or it used to work in Excel's Power Query in the past but haven't tested it in Excel now?

Best Regards,
Jing

[allan_t]

Thanks for helping on this. I just tested in Excel Power Query and am getting the same error message. In this Power Query Editor screenshot from Excel, the SharePoint list Submitter column displays as a Record and can be expanded, no issue.  The LeadAssigned column displays as a table and can not be expanded.  The LeadAssigned column is the one I'm having an issue with.   The Submitter column is a single person column type.  The LeadAssigned column is a multiple person column type.  Is there a way for me to retrieve the people list in Power Query for the data stored in the Lead Assigned column? 

 Here's the error I get with the LeadAssigned column when I try to click the table hyperlink in a row. 

When I click the Expand column icon for LeadAssigned, Power Query hangs for a few minutes, then gives this result: