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.

[rafael_inoue]

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

[ChrisAtMAF]

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:

https://minkus.medium.com/easily-connecting-between-power-query-power-bi-and-microsoft-graph-72333eb...

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

[JSR]

This works now for me directly using Odata feed..something has changed?

https://graph.microsoft.com/beta/groups

[Anonymous]

Hi, I Got errors on this one... When using it from the desktop it's working fine, but publishing tot  PB service then the connection has authentication errors.  

[cristianoag]

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/ 

[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/?

[jude_moore]

---

@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

[JanBakker]

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. 

[MrAnalyticy]

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

[sucharits]

Does this connector sample work with App only auth i.e. without an user in the context just using app's client_secret?

[tonmcg · Accepted solution]

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.

[Anonymous]

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"": [
            ""abc.def@ghi.com""
        ],
        ""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
    Source

I'm getting the below error:

Can anyone help me out with this?

Any help is highly appreciated.

Thank you.

[HairyDrumroll]

---

@tonmcg wrote:

Here's a tutorial

---

Link is dead.

[jprdynamicapps]

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

[shocko]

Thanks for posting this solution here. Seems odd that this is not native?!

[iseth]

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.

[sorinnicumarian]

There should be a default client app for Graph. https://docs.jamcracker.com/Orion/CSB%20SP/topic/Graph%20API%20Client%20ID%20and%20Graph%20API%20Sec...

[iseth]

Also, has anyone tried to make some sort of a expense tracking board by adding expenses involved in all cloud applications such as Dropbox, slack etc ... @vilmarci @Anonymous @tonmcg