- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Was able to import everything successfully, but when making the relationship connection using the ID value from one of the user columns to the UserInformationList provided the wrong name.
This isn't working and shouldn't be so hard.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have a simpler solution presented here: https://marqueeinsights.com/how-to-data-mine-a-sharepoint-list-with-power-bi/
It's not necessary to import multiple tables, etc.
Thanks!
Treb, Power BI MVP
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Connect to SharePoint online Lists with "GetData - SharePoint online list url=http://<mySharePointSite>/sites/sitename/
- 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"
- From manage relation icon make relation between the SharePoint task table "AssignToID" and UserInformationList "ID"
- From edit query icon:
- 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].
- back to Power BI desktop
- choose from right side under fields:
- “Title” from SharePoint task list
- “Name” from UserInformationList
- choose from right side under fields:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The instruction provided works if the look up is in a list within the SP site. However consider,
-SharePoint Column named "Employee Name" is set as Person or Group
-It is pulling names from another source such as Workday
-when using the expand option and picking the column, it pulled in the Employee Name
-when the dataset fresches, it does not pull the most recently entered data into the report. I can see the new entries in SharePoint List but not in the Power BI report.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you using this method?
https://marqueeinsights.com/how-to-data-mine-a-sharepoint-list-with-power-bi/
I've never had an issue getting the latest information doing it this way.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes. I am able to get the data in the columns to import using the expand option but my data is not refreshing. The dataset refreshes but it does not pull in any new entries from the SharePoint list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you refreshing via desktop or the Power BI Service?
If you are in the service, have you refreshed your credentials? Have you tried a manual refresh versus a scheduled refresh?
If you are in the desktop, if you refresh in the Power Query Editor, do you see any errors?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have refreshed from the desktop and app.powerbi and I have a scheduled refresh for 4AM and 4PM - none of these pull the new entries from the SharePoint List into Power BI. When I refresh in Power Query, there are not any errors.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try logging into the data source again to eliminate this as a potential cause.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, I had issues with the Ids not matching in the previous method. This solution is much easier and works.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks. This method solved this poblem, and many others I'll have in the future. Top dog !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Meagan - a while later and this really helped me!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To give the steps missing to the solution:
In my case I didn't have to merge the User Information list. The column in the list was of type user/group, and I just had to expand the column within PowerBI and select the columns I needed
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
05-06-2024 11:12 PM | |||
06-28-2024 05:52 AM | |||
07-24-2023 10:25 AM | |||
06-17-2024 09:57 AM | |||
07-10-2024 08:53 AM |
User | Count |
---|---|
141 | |
115 | |
82 | |
63 | |
48 |