Workaround to connect Power BI to Microsoft Graph

In the beginning of this year, I created some reports with Power BI for governance purposes. I used it to follow up on the creation of Office 365 Groups, the number of owners and members, if they contained guest users etc.
Since I have a technical background and I love coding, it was a natural choice to use Microsoft Graph as a data source for this (reuse, reuse, reuse!). All the information is in there, in ODATA format, and with 4 simple clicks it was imported into my Power BI report.

Out of the blue it completely stopped working. I was not the only one experiencing this, an issue report can be found on the Power BI Community. According to those posts, it apparently was never supported to connect Power BI and Microsoft Graph.
Now we've got an awesome dataset (not just the normal endpoints, but it even has a reports endpoint) and an awesome BI tool but no way of connecting them. Or is there...

Solution

The solution consists of two lousy Azure Functions functions. You host these in your Azure environment, make sure they have the correct permissions for Microsoft Graph and you use them for all your Graph queries.
You can find the source code and the installation instructions in this Github repository!

It's not a holy grail, but at least it is a viable solution until Microsoft releases a dedicated connector in Power BI for Microsoft Graph.

How it works

The two Azure Functions act as a proxy for the Microsoft Graph. You send your GET requests to one of the functions, it will pass the request to Microsoft Graph (use application permissions or on behalf of permissions, configurable!) and return the result.

Every GET request you can send to the Microsoft Graph is also possible with this Azure Function! It's as simple as replacing https://graph.microsoft.com with https://your-function-name.azurewebsites.net. It even supports ODATA querystring parameters like $select and $top, end distinguishes between v1.0 and beta endpoint.

In Power BI you just use these queries as an ODATA feed, you log in with your Organizational Account and your data will be loaded into your report. Since this is just an ODATA feed, it also supports scheduled refreshes in the Power BI service.

Example

If you want to select the id and displayname of all users in pages of 10 items to the beta endpoint, in Microsoft Graph:

GET https://graph.microsoft.com/beta/users?$select=id,displayName&$top=10

would become

GET https://{your-function-name}.azurewebsites.net/beta/users?$select=id,displayName&$top=10

Technologies

Azure Functions v2

With v2 I could specify two functions with catch all routes v1.0/{*uri} and beta/{*uri}, these wildcard routes aren't supported in v1 of the runtime. I tried creating one function with a wildcard route, but that breaks the function runtime (it tries to access an endpoint on the function url but with a catch all route, that request fails).

Power BI is smart enough to follow the @odata.context and @odata.nextLink links getting the metadata and the rest of the result set. The functions will rewrite those to contain a link to the Azure Function instead of directly to the Microsoft Graph.

Honorable mentions

Known Issues

The beta endpoint for /reports has the possibility to return JSON (instead of a CSV document), but Power BI cannot parse the result with error DataFormat.Error: We were unable to resolve the type name 'Collection(office365GroupsActivityDetail)' to an EdmType
Loading a lot of information from the Graph in a short period of time might get you throttled with a 429 Too Many Requests. Power BI doesn't handle this gracefully and just stops working completely.