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 😉
Pingback: Easy way to retrieve Teams data in Power BI via Flow (and other data from the Graph) – The BIccountant
Thank you for the article, and I wanted to ask if you had posted the more detailed explanation of how M was handling everything without any other languages being called?
I learned that that’s M-code, actually. So it should work as it is.
/Imke
Thanks for the article, Is it possible to download attachment as a file form Microsoft Teams meeting using Microsoft Graph in power BI
You can try this (not sure it will work, though).
– Select a storage that accepts POST request for saving files
– Pass the binary content of the downloaded attachment into the content-section of the body
– Have in mind, that PQ executes these commands usually multiple times (files will be overwritten)
See for more details here: https://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/