Forum Discussion
Use Microsoft Graph API as data source
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.
Resolver IIThe 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.
Great, thank you, I found it finally, only I was waiting for the warning sign from PowerBI and didn't come up. Indeed, the connector is there and working. I still find it strange that MS doesn't provide a more simple way to access this data.
- mahoneypat
Microsoft Employee
I will be going through this tutorial to make a .mez file. Before I do that, can someone just post a generic .mez that we all could use (with our own Azure AD app credentials? Not how it works?
Regards,
Pat
mahoneypat There is a sample over here: https://github.com/microsoft/DataConnectors/tree/master/samples/MyGraph
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?
Thanks, it is working :)
I added the parameters in an extra file like:
{
"schedules": [
"[email protected]",
"[email protected]"
],
"startTime": {
"dateTime": "2019-03-01T09:00:00",
"timeZone": "Central European Time"
},
"endTime": {
"dateTime": "2019-04-01T09:00:00",
"timeZone": "Central European Time"
},
"availabilityViewInterval": "15"
}And indeed, if I add this like:
source = Json.Document(Web.Contents(url,[ Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(params) ] ))
I get the data I want :)
Hi this might be a stupid question, but what would the client id be ? as far as i know i am just connecting it to graph api so there is no app involved.
There should be a default client app for Graph. https://docs.jamcracker.com/Orion/CSB%20SP/topic/Graph%20API%20Client%20ID%20and%20Graph%20API%20Secret%20Key.html
Thanks for posting this solution here. Seems odd that this is not native?!
- jprdynamicapps
Helper IV
Can you share details on how you got this to work?
The MyGraph code threw ResourceAccessAuthoriationException.
I'm modifying it to use PKCE (is that what you used?), but haven't been able to get it working yet.
Thanks.
-jpr
Hello everyone,
I'm facing an issue while fetching data from Microsoft Graph API getSchedule method. It is a POST method.
Power BI giving me a 400 Bad Request error. I have registered an Azure AD App with all the required permission mentioned on the getSchedule documentation as below:
I am getting access_token as well in Power BI. I have written below the M Query code to call the API:
let token_uri = "https://login.windows.net/" & #"Azure AD Tenant ID" & "/oauth2/token", resource="https://graph.microsoft.com", tokenResponse = Json.Document(Web.Contents(token_uri, [ Content = Text.ToBinary(Uri.BuildQueryString( [ client_id = #"Azure Application Client ID", resource = resource, grant_type = "client_credentials", client_secret = #"Azure Application Client Secret" ] )), Headers = [Accept = "application/json"],ManualStatusHandling = {400} ])), access_token = tokenResponse[access_token], body= Text.ToBinary("{ ""schedules"": [ ""[email protected]"" ], ""startTime"": { ""dateTime"": ""2021-09-03T00:00:00"", ""timeZone"": ""UTC"" }, ""endTime"": { ""dateTime"": ""2021-09-03T23:59:59"", ""timeZone"": ""UTC"" } }"), Source = Json.Document(Web.Contents(#"Graph API Base Url" & #"Graph API Url Sub Part", [Headers=[#"Authorization"="Bearer " & access_token,#"Content-Type"="application/json;charset=utf-8"],Content=body])) in SourceI'm getting the below error:
Can anyone help me out with this?
Any help is highly appreciated.
Thank you.
