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.

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.

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:

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

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.