Connecting Power BI to Microsoft Graph

Long time ago I remember connecting directly to Microsoft Graph from Power BI using the ODATA connector. Even the Web Connector used to work ok when we used organizational auth. Then this week I saw a video created by Joao Lucindo (TSP here in Brazil) creating a custom connector just to extract information from Graph.

Immediately I thought that was a waste of time, then I figured out that due to a change in the OAuth flow required for some graph operations, the connection using ODATA connector was not working anymore. I learned from multiple posts in the Power BI tech community that the problem was common.

Well if the simple approach doesn’t work anymore, why not just create an app in Azure, give appropriate permissions, and connect using app/service mode customizing the connection in power BI? That is a possibility for any Graph operation that supports application permissions. The code is very simple and can be implemented for all operations that you see available when click + Add a Permission on the API permissions for the App registration in Azure.

Annotation 2020-05-28 140231

For sure you need to create an App registration, take note of Tenant ID, Client ID, and create a secret to allow access. Then keep in mind that only permissions available when click Application Permissions are possible with this approach.

In Power BI I created four parameters:

  • Azure Graph API Url: Contains the base URL to connect (below I’m using the beta URL)
  • Azure Tenant ID: This is the tenant ID you get when register the App in Azure (available on the overview page)
  • Azure Application ID: This is the client ID for the app you registered (available on the overview page)
  • Azure Application Client Secret: You need to click API permissions and create a secret value. Get the secret value to be used on the parameter.

Annotation 2020-05-28 140737

Then I created a function to generate the Application Access token (auth-v2-service token) following the requirements documented at https://docs.microsoft.com/en-us/graph/auth-v2-service. The detailed instructions to register the App and give permissions are also documented there.

Here is the code for AzureAccessToken M function:

let
     TokenUri = “
https://login.microsoftonline.com/” & #”Azure Tenant Id” & “/oauth2/token”,
     ResourceId = “
https://graph.microsoft.com”,
     TokenResponse = Json.Document(Web.Contents(TokenUri,
     [
     Content = Text.ToBinary(Uri.BuildQueryString([client_id = #”Azure Application Id”, resource = ResourceId, grant_type = “client_credentials”, client_secret = #”Azure Application Client Secret”])),
     Headers = [Accept = “application/json”], ManualStatusHandling = {400}
         ]
     )),
     AzureAccessToken = TokenResponse[access_token]

in

     AzureAccessToken

Then in the Power BI query, you can use either Odata.Feed or Web.Contents to return the result of the Graph query:

Annotation 2020-05-28 141516

What happens with the Graph operations that do not support application permissions? For example Microsoft Bookings related operations like list appointments cannot be returned using application based authentication, just with org User based authentication as you can see in https://docs.microsoft.com/en-us/graph/api/bookingbusiness-list-appointments?view=graph-rest-beta&tabs=http

Annotation 2020-05-28 141657

For those cases, we now need to follow Lucindo’ s approach and actually create a custom connector to implement the full auth-v2-user flow as described in https://docs.microsoft.com/en-us/graph/auth-v2-user.

You can check Lucindo’ s video and code at https://www.linkedin.com/feed/update/urn:li:activity:6670823169047306240/ and https://lnkd.in/giGnCBa. Great job João…

I created a sample connector as well and shared it on my GitHub at https://github.com/cristianoag/PowerBIGraphCustomConnector.

I also learned that data sets created from data returned from custom connectors need to have an On-premises Data Gateway to be refreshed automatically from the cloud service. Joao also shows how to install the custom connector and configure the gateway at https://lnkd.in/g59Tewr

Have fun, Cristiano.

Disclaimer – The information contained in this blog post doesn’t represent the official Microsoft guidance or best practices. It is just the view of the author on current alternatives, implementations and workarounds for common issues and/or business needs. Please refer to official Microsoft documentation and evaluate carefully any steps, code or procedures documented herein. The author doesn’t offer any warranty. Use this information at your own risk.

One-Time
Monthly
Yearly

Make a one-time donation

Make a monthly donation

Make a yearly donation

Choose an amount

$5.00
$15.00
$100.00
$5.00
$15.00
$100.00
$5.00
$15.00
$100.00

Or enter a custom amount

$

Your contribution is appreciated.

Your contribution is appreciated.

Your contribution is appreciated.

DonateDonate monthlyDonate yearly

2 comments

  1. Ooh, I’m close. I’m trying to do the initial part, but getting an error from Power BI Desktop for my Query:

    ‘Formula.Firewall: Query ‘GraphQuery’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.’

    Not quite sure what to make of that!

    Just a heads up, the parameters screenshot shows a trailing / in the base URL, and the Query1 screenshot shows a preceding / for the ‘/me’ component, which combined would give double slashes before the ‘me’ in the final URL. Only need one or the other.

    1. @Steve, sorry for the delay. I definitely need to check the comments more often. I just pushed a sample PBIX file with all the queries and parameters to GitHub. Check the file over there.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s