Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I'm having some issues using a custom connector to get what I want out of the Microsoft Graph API. Essentially, what I am looking to do is take the "Email Activity User Detail" report and turn it into a nice Power BI report for some people to use. The URL for that report is as follows:
https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D7')
In order to create this custom connector, I have been using the following example as I'm not very familiar with the use of Power Query, nor the Graph API:
https://github.com/Microsoft/DataConnectors/blob/master/samples/MyGraph/README.md
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 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?
Any assistance would be greatly appreciated.
You are using the v1.0 of the Graph API. This only supports getting the results of the report in csv format. this explains the 302 redirect you get which points you to the binary stream of the CSV file you can download.
The beta version of this supports specifying the return type of application/json:
here is the sample from the above page:
GET https://graph.microsoft.com/beta/reports/getEmailActivityUserDetail(period='D7')?$format=application/json
Hi,
You are using the graph api v1.0 which only supports downloading this data as a CSV file, which is why when you follow the 302 redirection.
If you use the beta version of the same reports, see first link below, MS has added the option to return the JSON vs. CSV which will work much better for PowerBI.
https://graph.microsoft.com/beta/reports/getEmailActivityUserDetail(period='D7')?$format=application/json
You may check if Binary functions help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |