Forum Discussion
Use Microsoft Graph API as data source
Dear Community,
I received a task to create a report about all free meeting rooms at a given time. As far as I can see, this can be achieved by accessing all shared calendars via the Graph API:
https://graph.microsoft.com/beta/me/findRooms
I have no exerience at all, but spent a half day to make the connection working without success. In graph exlorer I get the correct results, but I couldn't manage to make it work in PowerBI. When I try to connect, I get an error message: "Access to the resource is forbidden.".
I even created an API key, and added permission to use the graph api, but I never got any data, only errors. When I enter the api key, the error is that I can only use the api key when I use anonymous authentication, but I cannot go back and change the authentication anymore. Also, it seems totally random when I get the option to chose between authentication methods.
Please, someone give me the steps I need to make to get the data in PowerBI. Thanks.
The Microsoft Graph implements the OAuth 2.0 authentication flow and therefore, to access it with Power BI, you'll need to create a custom data connector. Here's a tutorial that walks step-by-step on how to create a custom data connector with OAuth 2.0 to the Microsoft Graph in Power BI.
30 Replies
tonmcgResolver II
The Microsoft Graph implements the OAuth 2.0 authentication flow and therefore, to access it with Power BI, you'll need to create a custom data connector. Here's a tutorial that walks step-by-step on how to create a custom data connector with OAuth 2.0 to the Microsoft Graph in Power BI.
HairyDrumrollAdvocate I
Thank you. I walked trough the tutorial, but stuck at the end:
"You can now build your project in Visual Studio to create a compiled extension file, and deploy it to your Custom Connectors directory. "
How does this work? I copied the "MyGraph.mez" file to "Documents\Power BI Desktop\Custom Connectors" folder changed the security settings, but still not appear as connector.
- tonmcg
That last part of the tutorial sort of hand-waves over important steps needed to get custom connectors working. After you've copied the .mez file over to your Custom Connectors folder, you'll need to lower the security level for extensions in Power BI Desktop to enable loading custom data connectors.
In Power BI Desktop:
- Go to File | Options and settings | Options
- Go the Security tab
- Under Data Extensions, select Allow any extension to load without warning or validation
- Restart Power BI Desktop
I took this from the Quickstart section of the DataConnectors GitHub repo.
Not sure I can ask this here, as it belongs a little to this question, but how can I use POST with this solution? Like the https://graph.microsoft.com/beta/me/calendar/getschedule query needs a header that I need to post to get the data?
I tried to find the answer in the documentation of OData.Feed. This says I can add the headers, but there's no syntax how to do this.
- tonmcg
Unless query folding is an absolute must, I would use Web.Contents instead. OData.Feed is quite buggy.
I believe the inclusion of a body in your Http request header instructs Power BI to automatically make a POST request rather than a GET request.
Outside of any tool, what should the structure of your request look like?
https://janbakker.tech/2020/05/09/use-graph-api-data-in-power-bi-using-logicapps/
There is a easy way to this this. Just use a Logic App for your HTTP query and Oath authentication.
As far as I understand Microsoft documentation on this, apparently, using Graph API in Power BI and in other use cases is "note suported"!!
https://docs.microsoft.com/en-us/power-query/connecting-to-graph
cristianoagMicrosoft Employee
I documented an approach to have Power BI consuming Graph data. There is a workaround using explicit app authentication for the operations that support that and a custom connector for the ones that only support user/delegated permissions. More details available at https://techpeanuts.wordpress.com/2020/05/28/connecting-power-bi-to-microsoft-graph/
cristianoag wrote:I documented an approach to have Power BI consuming Graph data. There is a workaround using explicit app authentication for the operations that support that and a custom connector for the ones that only support user/delegated permissions. More details available at https://techpeanuts.wordpress.com/2020/05/28/connecting-power-bi-to-microsoft-graph/
Great thanks cristianoag
Will take a look and let you know. Apologies. Login trouble
Regards
Jude
- HairyDrumroll
cristianoag wrote:...More details available at https://techpeanuts.wordpress.com/2020/05/28/connecting-power-bi-to-microsoft-graph/
404 Page not found
Did you mean https://tech-peanuts.com/2020/05/28/connecting-power-bi-to-microsoft-graph/?
I found a way to do this which involves connecting to Microsoft Graph using an Azure web application without having to muck about with Visual Studio. I hope it's helpful to others too:
- tonmcg
This should be the accepted answer. Extremely thorough tutorial that allows Power Query (Power BI, Excel, etc.) to interact with the Microsoft Graph API. Bravo!
(Now, all it needs is a way to decrypt that client secret. )
- HairyDrumroll
tonmcg wrote:This should be the accepted answer.
Except that it doesn't work for Delegated Permissions, which is what you need to be able to read the Microsoft ToDo data.
Does this connector sample work with App only auth i.e. without an user in the context just using app's client_secret?
JSRHelper II
This works now for me directly using Odata feed..something has changed?
https://graph.microsoft.com/beta/groups
Hi,
Have you a solution to fix this problem about access Forbidden in graph queries?
I have the same problem and no matter what I do, I just can't using this data source as a oData Feed.
Thanks
