Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
vilmarci
Frequent Visitor

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.

1 ACCEPTED SOLUTION
tonmcg
Resolver II
Resolver 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.

View solution in original post

30 REPLIES 30
rafael_inoue
Frequent Visitor

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
Frequent Visitor

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

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


@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
Helper II
Helper II

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

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

 

Anonymous
Not applicable

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
Microsoft Employee
Microsoft 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/ 

Anonymous
Not applicable


@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
New Member

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

sucharits
New Member

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

tonmcg
Resolver II
Resolver 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.

Anonymous
Not applicable

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:

Harsh_Jariwala_0-1629971955248.png

Harsh_Jariwala_1-1629972071319.png

 

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:

Harsh_Jariwala_2-1629972329181.png

 

Can anyone help me out with this?

 

Any help is highly appreciated.

 

Thank you.


@tonmcg wrote:

Here's a tutorial


Link is dead.

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
Frequent Visitor

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

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.

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 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.