How to display an assigned user in a sharepoint list in Power BI (o365)?

[Hemulen]

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.

[duncfair]

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]

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]

[video]

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

[kemmerrc]

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]

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. 

[kemmerrc]

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]

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?

[kemmerrc]

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]

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

[Caitlin_Knox]

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]

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.

[MaryM]

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

[acolmanj]

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

[Anonymous]

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]

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]

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

[Vicky_Song · Accepted 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.

[MWilliamsUSDA]

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

[egarres]

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

[IanC-SAC]

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