Use Graph API data in Power BI using Logic Apps

Some things in the modern connected world seem so common that you just assume it’s possible by nature. Getting your Microsoft Graph API data into Microsoft Power BI for example. That must be easy peasy right? Well….

When I start looking for ways to do this, I assumed there was a builtin connector available in Power BI that I could use. Guess what? There is not (yet). There is a connector for the Microsoft Security Graph, but that one “only” gives back the data from the security products. Just good to know that it’s out there, but that’s not what we’re looking for.

When you look for solutions on the internet I read about all these methods that work for a while and then breaks. An example of that is the use of the OData feed connector in Power BI. This used to work for a while, but all of the sudden users could not authenticate any longer. Turn out, that OData is not supporting OAth authentication (anymore).

So, I found this article from Yannick that’s a great workaround. It uses an Azure Function that acts as a proxy, so you have to re-write your queries to match your function URL. As this would work for sure, I was looking for an easier approach (no offense Yannick 😉 )

The next option is a custom power Query Connector. There is a good example of how to build this, but when I saw the Github issues related to this, I saw that this method also broke.

So I could not find any working solution for this. I discussed this with my colleagues and we came up with a way to get this done. I’m happy to share this with you guys (and girls). Enough with the chit-chat, on with the show!

Logic Apps to the rescue!

In this demo, I am going to show you how you can use Logic apps (or Power Automate if you prefer) to pull the data out of the Microsoft Graph API. Take a look at the overview of the solution.

1. Power Query in Power BI sends a POST HTTPS request to kick off the Logic App.
2. The Logic App uses an HTTP connector with Oath authentication to get the data out of Graph API.
3. The JSON data is sent back to Power BI.

App registration

Earlier, I wrote a blog about Power Automate where I showed all the steps to create an app registration. I don’t go over all the steps here. You can use the previous blog as a reference if you’re not familiar with app registrations, API permissions, and secrets.

We need an app registration in Azure AD to authenticate to Graph API. Create the app registration and add the right permissions. To see what permissions you’ll need, head over to the Graph API reference. In this example we are getting the Sign-in logs as an example, so we’ll need to add the AuditLog.Read.All and Directory.Read.All permissions. Don’t forget to grant admin consent after you applied the permissions.

When the app registration is created, create a secret. When done, write down the properties of the app registration for later use.

• Application (client) ID
• Directory (tenant) ID
• Secret

Logic App

Next, we are going to create the Logic App. Create a new Logic App and pick the trigger: When a HTTP request is received

Use the following JSON body Schema:

{
    "properties": {},
    "type": "object"
}

When you save the trigger, the HTTPS POST URL will be created. We are going to use this later.

Our next step in the Log App is the HTTP request to call the Graph API. For this example, I calling for the Sign-in Logs using: https://graph.microsoft.com/beta/auditLogs/signIns. For authentication, pick Active Directory OAuth and fill in the client ID, Tenant ID and secret from app registration.

Don’t forget to enable pagination if you request large amounts of data.

Our final step is to create a response. Just pick the body content from the HTTP request.

Your final Logic App will look like this:

Power Query

Head over to Power BI Desktop to create our Power Query.

Switch over to the Advanced Editor and create the query.

let
    url = "https://your_logic_app_endpoint_post_url",
    body  = "{}",
    Parsed_JSON = Json.Document(body),
    BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(BuildQueryString) ] ))
in
    Source

Replace the URL with the URL from your Logic App trigger.

When the query asks for credentials, just pick an anonymous connection.

Next, we need to parse the data and convert it into a table.

You can then expand the data to new rows and expand the columns. Depending on your data, multiple values need to be expanded.

You can expand all the columns and only select the data that you care about. The steps will look like this, depending on what data you are working with:

You can now go on and take care of your dataset to use it in your Power BI reports. Rename the query to a recognizable phrase to keep the overview.

When done, click Close & Apply.

You can now use the data to build your reports. I suck at Power BI, but I’m sure someone can build some nice reports with this data 😉

Wrap up

Using Graph API data in your Power BI reports can give some great insights. The sky is the limit here. I assume, since you stumbled upon this blog, you got your own use-case. To give you some ideas:

• You can pull all your users and groups from Azure AD, with all extended attributes
• You can pull usage reports to keep track of your adoption strategy
• List all Teams
• List all your Conditional Access policies

TIP: you can clone your Logic App. I won’t recommend building more than one data request behind each trigger.

I bet you can do this better!

I’m sure there are more ways to do this. I’d like to hear back from you. Do you think you have something way better? Let me know!

I’ve also found this workaround. With this solution you’ll have to install the Power BI Gateway, but auto refresh will work.

Update 10-05-2020 ( Azure Blob Storage)

You can also use an Azure Blob Storage Container (or basically any data source) to store the JSON data. This way you can handle much bigger chunks of data. Now, I am not going trough all the steps here, but to give you an idea of how this would work, I created this overview:

Just create a new storage account in Azure and build your Logic App like this:

It will drop the raw JSON data output of the HTTP request into a new blob. Whenever you re-run this Logic App, the blob is replaced by default. In Power BI, use the Azure Blob Storage connector to connect to your data source.

Fill in the path (minus the / at the end).

Next, enter the access key.

Repeat the earlier steps to parse the JSON into tables.