Forum Discussion
SharePoint List - user fields do not translate
We have a Task Tracker list in SharePoint Online that we want to create some reports in Power BI on. One of the main fields in the list is "Assigned To" which is a "Person or Group" type.
I can connect to the list using Power BI, but the Assigned To field does not get pulled in properly. Instead, it appears to create two columns with numerical data, not user data:
How can I get my actual user information to show up in Power BI? Is there some sort of translation I need?
4 Replies
I suspect that this functionality has changed since the original post. When I query a SharePoint list, Power Query allows me to expand a Person field and specify which properties to display.
The problem I ran into relates to people that are no longer in the organisation. These rows were being filtered out from the results. The workaround I found was to expand the FieldValuesAsText column instead and select the Person fields I wanted returned. This ensured that rows with previous employees were included in my results.
My guess is that the field is actually storing the ID of a record of another table's. Is there another list of Assignees? If so - you want to also have that table and join them on the ID - in order to get the plain english information.
This field is coming straight from AD as far as I know.
Person and group type columns in SharePoint list are lookup columns which fields lookup into another SharePoint list, so you will get the value expressed as "Record". And the "Id" value you get actually is user id in the site collection. You can import site collection user profile information and join it to your SharePoint list and you should be able to get the user information.
Here are some useful links for you reference.
http://community.powerbi.com/t5/Desktop/How-to-display-an-assigned-user-in-a-sharepoint-list-in-Power-BI/m-p/32580#M11371https://whitepages.unlimitedviz.com/2016/07/simplified-method-working-sharepoint-data-power-bi/
Regards,
Charlie Liao
