How to get more out of your Graph API custom connector in Power BI

Edit: Microsoft has announced that they don’t support querying MS Graph in Power BI: Lack of Support for Microsoft Graph in Power Query – Power Query | Microsoft Learn

The Graph API can deliver a huge amount of interesting data from your Microsoft 365-universe, but the Graph API custom connector for Power BI is not able to retrieve everything from it in its current shape. So I’ve modified it a bit to squeeze out a bit more of its sweet juice.

Problem

When trying to get the details for planner tasks, the following error-message appears:

Solution

Add another function to the connector that uses the Web.Contents-function instead of the OData.Feed-function.

Make sure to include the metadata-record from row 48. As without it, the Data Source Kind definition-record, which provides the authentication-details, would be ignored.

To make this work, you have to create another DataSource.Kind-record, as you can only use the same record for functions that have the same required function parameters. And this is not the case here, as the new function requires a URL, while the first function doesn’t require any arguments at all.

Also you need a second Publish-record if you want this function to appear as a separate connector in the UI:

 

This feels odd, doesn’t it?

If you haven’t done much with custom connectors yet and are halfway fluent in the M-language (but no other programming languages), you might wonder how on earth that record from row 48 delivers its content to the following function. Also, you might ask yourself how/why the content of the MyGraph2-DataSource.Kind-record can be retrieved by putting the variable name into “”, instead of using it like you’d do in Power Query so: DataSource.Kind=MyGraph2.

But if you haven’t, maybe you have instead fiddled around with the functions fancy variable name that contains a “.” and changed its first part to something nicer.

All this might trigger the moment where you realize that some other language must be involved here. And exploring the files in the .mez-folder gives a hint on what that could be:

I haven’t found any documentation about this yet, but if anyone has, please send link and I will include that here.

Edit 17th April 20:15 UTC: There is no other language involved. It’s all M. Will update once I can explain how this works.

How to use

You have to provide a valid URL as the only parameter to this new function and it can easily be retrieved from the Graph-Explorer

This will return this sample-URL: https://graph.microsoft.com/v1.0/planner/tasks/{task-id}/details

Just replace the placeholder “{task-id}” with a string from the task to query. (It’s easiest to use if you start with the standard OData-function and then use the Web.Contents function only where needed. In the sample above, you’d add a column where you call the new function and reference the column with the task-id as the URL-input-parameter)

Enjoy & stay queryious 😉