CODEWITHSUNDEEP
microsoft-graph-apipowerbipowerquery
Xileize
Xileize

Reputation: 23

Getting Microsoft Graph API Report Data into Power BI using a custom connector

I'm having some issues using a custom connector to get what I want out of the Microsoft Graph. Essentially, what I am looking to do is take the Email Activity User Detail report and turn it into a Power BI report.

The URL for that report is as follows:

/v1.0/reports/getEmailActivityUserDetail(period='D7')

In order to create this custom connector, I have been using the MyGraph Connector Sample as I'm not very familiar with the use of Power Query, nor the Graph API.

I have created the connector and can connect to the API and everything, but rather than return me the data, all the query does it return me a byte array I can't seem to do anything with.

From looking at Fiddler traces, the actual data I want is being picked up along the way, but it looks like the Graph API uses a redirect URL to the download, which I'm not sure my Power Query code is handling. I think it may just be returning the redirect URL as a string rather than the actual data.

Further to this, my query has no code to handle the NextLink if it goes over the number of returned rows (I think 200?), so I imagine it will not return the full dataset once I do get it working. How is this best handled?

Unfortunately, I am very new to Power Query (this is my first encounter with it, in fact), so I'm not sure where to begin to make the necessary changes to get what I need.

Any assistance would be greatly appreciated.

The Microsoft Graph API - GET Reports in Graph Explorer and Power BI question touches on a similar issue, though that is through the Application interface itself, which seems to be unable to get the necessary authorization tokens by itself.

EDIT: I have managed to fix the issue thanks to the advice from Marc LaFleur.

The problem was indeed that I was trying to use the Odata.Feed as the source. Changing the line:

source = OData.Feed("https://graph.microsoft.com/v1.0/me/", null, [ ODataVersion = 4, MoreColumns = true ])

To:

source = Csv.Document(Web.Contents("https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D7')"))

Fixed the issue and returned the correct values with no other changes.

Upvotes: 2

Views: 2444

Answers (1)

Marc LaFleur
Marc LaFleur

Reputation: 33114

Microsoft Graph's Reports are not traditional REST endpoints. Rather than returning OData/JSON, they return a temporary URL to a physical file in CSV format.

From the documentation:

If successful, this method returns a 302 Found response that redirects to a preauthenticated download URL for the report. That URL can be found in the Location header in the response.

From the example you linked too, it looks like it is expecting an OData.Feed source (source = OData.Feed). In this case, you're not getting an OData result but rather a URL to a CSV file. I believe you're looking for Csv.Document instead.

Upvotes: 2

Related Questions