Connecting to Microsoft Graph API to report on Email Activity, Do I need an authentication token?

[BroomJ]

Hello,

At the very core, I'm essentially aiming to recreate the Office 365 Admin Center Email Activity report that shows the number of Send, Receive and Read actions on emails for each user within Power BI.

I have located a report within the Microsoft Graph API that appears to be similar (though if anyone knows specifically where I need to be looking, any guidance would be wonderful), but I get an error stating "Access to the resource is forbidden." when I try to use the following URL with an OData Feed Data Source:

https://graph.microsoft.com/beta/reports/getEmailActivityUserCounts(period='D180')?$format=applicati...

I can access this Report and receive data back fine within Microsoft Graph Explorer, and I think the reason it's not working is because I require some sort of Authentication Token or need to send the necessary Headers with my OData Feed Source, but I'm not entirely sure how to using the "Blank Query" source.

If anyone has any knowledge on how to do any of the above it would be greatly appreciated.

Thanks.

[xjr]

By any chance did you get anywhere with this?

This is something I will be working on.

[BroomJ]

Apologies for the delay, I have been rather busy and only just managed to pick this back up.

Upon further research, I managed to find the following article:

https://github.com/Microsoft/DataConnectors/blob/master/samples/MyGraph/README.md

Following this has allowed me to create a custom connector but upon testing, I can only get it to return a byte array rather than the actual data in a usable format.

I'm using the power query code on that page, but I think it's missing some steps to use the next link and download URL redirect correctly.

Unfortunately, my power query knowledge isn't the greatest so I'm not sure how to get around these two issues.

Here is what Visual Studio returns if I run the query:

Would it be wiser for me to move this over to the Developer forum rather than the Desktop one?

[v-yuezhe-msft]

@BroomJ,

Yes, it is more appropriate that you post the question in Power BI Developer forum when you create custom connectors.

Regards,
Lydia

[v-yuezhe-msft]

@BroomJ,

Please review my reply in the following similar  thread.

https://community.powerbi.com/t5/Desktop/Power-BI-Desktop-Unable-to-pull-from-OData-feed-using-Token...

And you can follow the instructions in this article to get your access token.

Regards,
Lydia

[BroomJ]

Hi Lydia,

I did see both your previous post and the documentation, but I'm not 100% familiar with how to set up the access/token request for use with Power BI specifically. 

If you would be able to point me in the right direction that would be greatly appreciated.

[v-yuezhe-msft]

@BroomJ,

Replace <Your token> in the following code with your token, then paste the code to the Advanced Editor of a blank query, do you get any error messages?

let 
Source = OData.Feed("URL", null, [Headers = [Authorization = "Bearer <Your token>" ] ])
in
Source

Regards,
Lydia