cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hemulen Frequent Visitor
Frequent Visitor

How to display an assigned user in a sharepoint list in Power BI (o365)?

We have a salespipe list in SP o365 which i want to make an report from.

There's users assigned to leads etc in this list.

As sharepoint o365 doesnt have a userlist available (only for SP admins i think) to make a relationship with (ie a username or an email address) in Power BI this is a problem.

 

How can i retrieve a list with users from sharepoint to match with the record id in Power Bi?

When i choose name or id it only shows the "record" or "id" not the real name of the user.

1 ACCEPTED SOLUTION

Accepted Solutions
Vicky_Song Established Member
Established Member

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

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.

 

1). Import SharePoint list. (you should already finished it)

2). Import user profile information: 

2.1). Get data with OData type: https://sharepointsitename.sharepoint.com/_vti_bin/listdata.svc (change sharepointsitename to your site name)

2.2). Select the list “UserInformationList” and load the data.

3). Join SharePoint list you imported in Step1 to user profile info you get in Step2: Go to Manage Relationships function in Power BI Desktop.

16 REPLIES 16
Vicky_Song Established Member
Established Member

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

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.

 

1). Import SharePoint list. (you should already finished it)

2). Import user profile information: 

2.1). Get data with OData type: https://sharepointsitename.sharepoint.com/_vti_bin/listdata.svc (change sharepointsitename to your site name)

2.2). Select the list “UserInformationList” and load the data.

3). Join SharePoint list you imported in Step1 to user profile info you get in Step2: Go to Manage Relationships function in Power BI Desktop.

Hemulen Frequent Visitor
Frequent Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

Thanks. But if there's new users i need to regularly import a new list with users.

I solved it by creating a workflow updating a new field with the name of the user. Retreiving the info from the assigned user. In this way i don't have to update a list with users.

 

Thanks anyway.

 

 

MarcNavarro Frequent Visitor
Frequent Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

Hello,

When I connect to your URL Power BI show me this error: Details:

"Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Resource not found for the segment '_api'.)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Resource not found for the segment '_api'.)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Resource not found for the segment '_api'.)"

 

But if I navigate using IE to the URL it show me the XML format data. Any idea?

 

Thanks.

 

kelbow Frequent Visitor
Frequent Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

And then what? I imported the user info list successfully and joined it to the other list. But...I still am only getting IDs and using the "title" dimension of the user info list doesn't give the right behavior either.

MasterShake Frequent Visitor
Frequent Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

I am so sick and tired of partial, cryptic answers!!!!!!!

MasterShake Frequent Visitor
Frequent Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

I am so sick and tired of partial, crypic answers!!!!!!!! 

hollyketchel Frequent Visitor
Frequent Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

I also get the error

 

OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Resource not found for the segment '_api'.)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Resource not found for the segment '_api'.)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Resource not found for the segment '_api'.)"

 

Any resolution?

hollyketchel Frequent Visitor
Frequent Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

Late perhaps but I found this post very helpful:

 

http://christine-payton.com/accessing-sharepoint-user-profile-data-with-power-bi/

 

 

Meagan Regular Visitor
Regular Visitor

Re: How to display an assigned user in a sharepoint list in Power BI (o365)?

Very late reply, but I prefer this method: https://whitepages.unlimitedviz.com/2016/07/simplified-method-working-sharepoint-data-power-bi/ 

 

Instead of going to the extra table to join, there is a FieldValuesAsText field that you should see in your list. Use that, convert to table, then expand the list. This should give you the names of the people in your field without having to join to anything. It may also save you a coupel steps if you have other fields that are lookups/dropdown boxes/etc.