Use Microsoft Graph API as data source

[vilmarci]

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.

[vilmarci]

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?

[vilmarci]

Thanks, it is working 🙂

I added the parameters in an extra file like:

{
"schedules": [
"room1@my.com",
"room1@my.com"
],
"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 🙂

[jude_moore]

Hi

vilmarci

   - How did you do this please ? could you please share a pbit or generic example? Ive been stuck for months - many thanks - Jude Moore

[Anonymous]

Hi, Did the solution worked for you? I have tried to develop the solution in visual studio but I am having troubles, I don't have any experience in M, Would you mind sharing me your project in order to take a look?

Thanks

[vilmarci]

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.

[mahoneypat]

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

[JanBakker]

@mahoneypat There is a sample over here: https://github.com/microsoft/DataConnectors/tree/master/samples/MyGraph

[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:

1. Go to File | Options and settings | Options
2. Go the Security tab
3. Under Data Extensions, select Allow any extension to load without warning or validation
4. Restart Power BI Desktop

I took this from the Quickstart section of the DataConnectors GitHub repo.

[vilmarci]

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.