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