cancel
Showing results for 
Search instead for 
Did you mean: 
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

20 REPLIES 20
yradi
Regular Visitor

Ref. my video describe the following steps: @youtube https://m.youtube.com/watch?v=ugoHcHSY8lg&feature=share 

Dears,

follow the following steps to make relation between your users’ ID and their details which it’s in other table:

From Power BI desktop:

  1. Connect to SharePoint online Lists with "GetData - SharePoint online list url=http://<mySharePointSite>/sites/sitename/
  2. Connect to User Information List in SharePoint via OData​. from Power BI "GetData - OData feed" to access the information url="http://<mySharePointSite>/_vti_bin/listdata.svc" then select "UserInformationList"
  3. From manage relation icon make relation between the SharePoint task table "AssignToID" and UserInformationList "ID"
  4. From edit query icon:
    1. select your SharePoint online task table and edit your AuthorIdEditorIdOData column by Click on small icon in the column header and select [Expand to New Rows] text box, then Click on [Close & Apply].
  5. back to Power BI desktop
    1. choose from right side under fields:
      1. “Title” from SharePoint task list
      2. “Name” from UserInformationList

 

I've grabbed the User INformation list and made the connection to my sharepoint list, however custom fields i've added to the sharepoint user profiles don't come through as available attributes. Does anyone have any ideas?

Meagan
Advocate III
Advocate III

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.

 

 

SusieT
Regular Visitor

This worked perfectly for me and you can also duplicate the column before editing if you have more than one transformation required. Thanks so much for your help. 

The original post seems to be using the original OData connection to SharePoint instead of the SharePoint Online List Connector. My blog post here shows you how to use that to connect to SharePoint. https://marqueeinsights.com/how-to-data-mine-a-sharepoint-list-with-power-bi/

 

One of the nice features is that People/Group columns are returned as separate columns. You have to expand them to get to all of the relevant data but it makes it very easy to use without requiring an column duplication or other tricks. 

 

My next blog post in the SharePoint series will show in detail how to get to this information.

 

--Treb

 

YvetteM
Frequent Visitor

Thanks Meagan - a while later and this really helped me!

 

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

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!!!!!!!

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

jdoshi65
Frequent Visitor

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/

 

 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors