Power Query – User Power BI or Excel to connect to Office 365 Service API

Using Power Query within Power BI and within Excel has many uses. In this conceptual example, I will make data from the Office 365 Service Communications API available within excel so we are able to check the service status of each cloud service. At a glance we can then determine if there is any Service Degradation affecting an Office 365 Service.

Here is a method (MFA bypass required) you can use to get at the data in which you can then model:

Azure App to Allow API Communication

Firstly, you will need an Azure App registration set up with permissions to access the API. At a minimum, the delegated permission for ServiceHealth.Read is required.

Once you have the App set up, you will need to create/retrieve then capture the following values:

  • Tenant ID
  • Client ID
  • Username
  • Password
  • RedirectURI
Read More