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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors