Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to Solution.
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.
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)"
Regards
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
141 | |
114 | |
111 | |
59 | |
59 |