Help with Pulling Consultant Calendars into Power BI via MS Exchange or Outlook Help with PullingAPI

[Zack92]

Hey everyone,

I'm working on setting up a centralized calendar with all my consultants. As the first step, each consultant gave me access to their calendar with "View all details" permission. Then, I added them to my calendar. When I click on their names, I can see their schedules and events. 
People with their Calendars

Now, I'm trying to pull all the consultants' calendar events into Power BI using my MS Exchange Connector. I signed in, but I’m not seeing any of the consultants' calendars. Can anyone explain why this is happening? They’re all on my calendar now.

This is what I see when I connect 

Import Calendar data, I don't see any Consultant info

How can I get all those calendars into Power BI? If we can't access the data through the MS Exchange connection in Power BI, could we use the Outlook API to get the data? Does that work or are there any hidden settings? I am not an expert please advise me.

Thank you so much 

[tharunkumarRTK · Accepted solution]

@Zack92 

I think you should consider using Outlook graph API, 

GET https://graph.microsoft.com/v1.0/users/{user-id}/calendar/events

I would suggest you create a service principal and grant the necessary permissions to it and then run the above API from Power BI, You can generate the bearer token for the service principal and then pass it as a header to the graph API. 

Considering you want to retrive the calendar information of muliptle users, I would suggest you to create a M function and loop the function over the list of users. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[v-xiaocliu-msft · Accepted solution]

Hi @Zack92 ,

Here is the method how to call the graph api in power query.

https://community.fabric.microsoft.com/t5/Desktop/Getting-SharePoint-activity-report-into-Power-Bi/m...

You just need to make some changes to the code.

1\ Use the API to get the calendars and the API to get the events to replace the API in the code

2\ Add more delegated permissions to application registered in entra id.

Best Regards,

Wearsky

[tharunkumarRTK · Accepted solution]

@Zack92 

I think you should consider using Outlook graph API, 

GET https://graph.microsoft.com/v1.0/users/{user-id}/calendar/events

I would suggest you create a service principal and grant the necessary permissions to it and then run the above API from Power BI, You can generate the bearer token for the service principal and then pass it as a header to the graph API. 

Considering you want to retrive the calendar information of muliptle users, I would suggest you to create a M function and loop the function over the list of users. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Zack92]

@tharunkumarRTK Thank you for the response, Yes I have multiple users (around 40 users), and I will contact you about work I would like to do a consultation with screen share so I can get an idea. I will call you on Monday. Thank you