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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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

36 REPLIES 36

Thanks @Vicky_Song Finally i manage to display user name instead of ID

Anonymous
Not applicable

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. 

Anonymous
Not applicable

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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