Home Power BI Access Microsoft Graph API using Power BI

Access Microsoft Graph API using Power BI

by Prathy Kamasani

Recently I came across Microsoft Graph API through Power BI Custom data connectors documentation. Amazed by the amount of information provided by this API. I didn’t find an appropriate use case right away, but later I found a very interesting one “a Power BI template to find all Power BI users and their Groups”. In this post, I am going to cover a bit about Graph API and how to extract data into Power BI, more like an antecedent for my next blog post.

Microsoft Graph API is a RESTful Web API; we can use this to get access to data from the Microsoft Cloud services like Active Directory, Sharepoint, Onedrive and much more. You can find all documentation here – https://developer.microsoft.com/en-us/graph/docs/concepts/overview. What one of the main benefits of this API compares to many other Microsoft API’s is it has a single endpoint https://graph.microsoft.com.  I could use this as a data source in my Power BI Desktop, publish the Model to Power BI service and schedule the data source. Currently, we can not schedule Power BI custom data connectors, but once this feature gets implemented, I think it is better to access Graph API using custom data connector as documented here –  https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph

For now, if you want you can extract data from this API using OData Connector. The best place I found useful to start with Microsoft Graph API is the Graph Explorer – https://developer.microsoft.com/en-us/graph/graph-explorer

Microsoft Graph now supports two versions. I found beta useful as it has more data than version v1.0. You can switch between versions easily in Graph explorer and see what data available in each version. A user needs the right level of permission to get access to the correct data, and these permissions are at very granular level. I don’t think; I understood permissions very well if you want you can find permissions information here – https://developer.microsoft.com/en-us/graph/docs/concepts/permissions_reference

You can use the Graph Explorer, to see what data is available to view, modify permissions or just to get an overview of the API.

Access Microsoft Graph API using Power BI

Microsoft Graph has a unified OData endpoint, so I can just use this as OData Feed data source connection, and with few simple clicks, I could import data int Power BI Desktop.

I just copied the API URL from Graph Explorer, pasted in the OData feed data source. I used organizational account authentication. That’s all; I can see all users 🙂

You can download a simple template here – https://1drv.ms/u/s!Avm7gbgZtlMlqCk02OFlwX2ANOfs 

Summary:

This is a fascinating data source, and it gets more interesting with Power BI. As I mentioned earlier, I started playing with this API to get an overview of all Power BI users available. One can get a lot of insights from this data source, hope you find it useful.

Prathy 🙂

 

You may also like

9 comments

Damien McCarthy 15 October 2019 - 4:17 pm

this would be a great blog, do you have a link to it ?

Reply
Jan Izydorczyk 29 July 2019 - 2:39 pm

Hello, Since June 2018, PowerBI will not connect to this source through OData.
https://community.powerbi.com/t5/Desktop/Microsoft-Graph-Access-to-the-resource-is-forbidden/td-p/430580
Don’t you know any solution?

Reply
Prathy Kamasani 29 July 2019 - 6:09 pm

Hi, it’s not accessible using OData but you can use normal API. I will see if I can write a blog post about it…

Reply
mohan 221213 28 March 2018 - 4:59 am

How can i access the calendar data by using Graph API.

Reply
Jared 17 November 2017 - 9:17 pm

it’s good, but not all of it works easily in PowerBI, for example the sharepoint stuff from graph api doesn’t work because of authentication issues.

Reply
Prathy Kamasani 19 November 2017 - 7:54 pm

I have not tried with all data points, but users and groups info is pretty easy using Power BI. Hopefully, with next version, they will make it easy for SP too…

Reply
Jared 20 March 2018 - 3:59 pm

it’s good, but not all of it works easily in PowerBI, for example the sharepoint stuff from graph api doesn’t work because of authentication issues.

Reply
Prathy Kamasani 20 March 2018 - 4:00 pm

I have not tried with all data points, but users and groups info is pretty easy using Power BI. Hopefully, with next version, they will make it easy for SP too…

Reply

Leave a Reply