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
duncfair
Advocate II
Advocate II

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.

trebgatte
Most Valuable Professional
Most Valuable Professional

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

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

 

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. 

trebgatte
Most Valuable Professional
Most Valuable Professional

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. 

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. 

 

trebgatte
Most Valuable Professional
Most Valuable Professional

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?

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. 

 

 

 

trebgatte
Most Valuable Professional
Most Valuable Professional

Try logging into the data source again to eliminate this as a potential cause.

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
Super User
Super User

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.

 

 

Thanks, I had issues with the Ids not matching in the previous method. This solution is much easier and works. 

 

Thanks. This method solved this poblem, and many others I'll have in the future. Top dog !

Anonymous
Not applicable

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. 

trebgatte
Most Valuable Professional
Most Valuable Professional

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

 

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.

@Vicky_Song , this solution was music to my ears! Thanks for the help! 

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

 

egarres_0-1686742988176.png

 

@egarres ~ this solution worked for me. Thank you.

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.