Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Hemulen
New Member

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
Vicky_Song
Impactful Individual
Impactful Individual

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.

View solution in original post

35 REPLIES 35

Hi 

I used the Get Data / OData feed to import the list but it results in the following error:

 

Unable to Connect

 

We encountered an error while trying to connect. 

 

Details: "Micrsofot.Mashup.Engine1.Library.Resources.HttpResource: Request failed:

OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)

OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)

OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)"

nasiralisher_0-1594139244366.png

 


Regards 

Anonymous
Not applicable

Hi Vicky,

 

Please could you explain me in more details to import users data in order to display users name instead of ID.

 

Thanks in advance

This is not a solution as the end result is exactly the same. If I select any of the fields after setting up the relationship, it still shows numeric values instead of the user's name. 

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.

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

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

Yes!!!  You are sooo right.  Crptic answers with not very good solutions!  I was able to successfully import the User Information List.  However, when it was done, there does not seem to be any relationships.  I tried auto-detect... nothing.  I tried manually...  nothing.  I am at a loss as to why we would look for a User ID to compare.....

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.

 

Maybe You are trying to import data from Sharepoint list

 

https://xxxx.sharepoint.com/teams/JayPatel(ORv2.01)/Lists/ ** I was getting same error while importing data from this link

 

https://xxxx.sharepoint.com/teams/JayPatel(ORv2.01)/ ** Try this one. Remove the List from link. 

 

Let me know if this not work!!

 

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?

Hi hollyketchel

 

How did you overcome the 404 error?

 

My PowerBI report was working fine until about a week ago and now it cannot see the user information list on my sharepoint site.

 

Thanks

Provide SharePoint site URL instead providing List URL

Late perhaps but I found this post very helpful:

 

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

 

 

this site seems to have been taken down.

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.

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.