import Sharepoint online users and site groups into power BI Desktop

[gsg121]

Hi,

I am admin on Sharepoint online site and trying to read users/accounts and which group they belong to. so far I am able to read user info from 'User information List' table. which I believe is not really the best approach as I am having further problem when try to expand the tables. I am getting 'Permisson error' and unable to expand RoleAssignments column. 

Wondering is there any other way to get Sharepoint online users/Site Groups in Power BI i.e. Sharepoint List Url Could I use etc?

Many thanks in advance.

Best Regards

Guru

[djnww]

Is that you, Glenn ? I may have the wrong person... sorry.

I don't have an answer for you. However, the problem that you are reporting is a common problem that Power BI users face when importing data from a URL within Sharepoint.

From memory, the issue could be:

1. Security related

2. URL related. Power BI Desktop is pretty dumb. From memory, if you copy & paste an auto generated link in Sharepoint, the URL will return what you have just shown. You need to specify the actual tennant and page within the URL. There are a few articles/threads on it. FYI, I got Power BI to read an Excel document in Sharepoint. I am assuming that it can read a List.

- Dan

[gsg121]

Hi Dan,

Thanks for your reply.

I am not Glen, however the problem may be the same.

from my investigation the table User Profile List is hidden list/url, generally admins access it using http://mysite/_catalogs/users/simple.aspx and it lists only users who logged/accessed the system. so its pretty useless and will not give me details if a user never logged in!

I am thinking of doing the old way of running PowerShell script to export users/site Groups to csv file, then import csv into power BI and If I need fresh user list every day, could setup a job in scheduler over-writing csv.

PLease share if someone out there knows better way

[Phils]

Hi Guru,

Any update ?

Best regards,

Phils