Power Query Office 365

Posted on by



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.

I have an email coming in like shown in the snippet below and I would like to parse the text to be able to create a new row in a table in Excel that has matching columns. Is this something I can do with Power Query? I'm running Office 365 Business Premium with Excel 2016. I looked for the Exchange connection that you mentioned but I don't see it. Microsoft Power Query for Excel - Easy access to Office 365 data and other connectors Today i tested the new version of the ' Microsoft Power Query for Excel Plug-in ' and have to say ' Microsoft did a brilliant work on call External data from Excel '.

Microsoft excel power query

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

NB: This blog will not explain the creation of Azure App Registration but see this link if you would like more information on Create Azure App Registration for Accessing Office 365 Service Communications API.

Let's jump to Microsoft Excel desktop version and open a new blank workbook. Drivers money controls port devices.

Using Excel and M Power Query to Pull Data from API

Power Query Office 365 Mac

  • On the Ribbon, choose Data and then From Other Sources then Blank Query.
  • The Power Query Editor will now open and firstly, we will start with going to the Parameters section of the Ribbon and choosing Manage Parameters.
  • Populated 5 parameters as shown in the image below

NB: In this method, the Username & Password will be of an account that has permission to view the service activity.

Download Power Query Office 365

Power Query Office 365
  • Go to Advanced Editor and paste in the code below

On Saving the data you should see that you are now able to edit the data within Power Query Editor.

You are now able to model the data within Excel.

Should you now want to create more complex queries or hide colums etc you are able to step on for here.

Power Query Office 365 Online

Once you click Close and Load, this is the end result:

This process can be easily manipulated to target other Office 365 Management APIs. Feel free to adapt this model to suit your requirements.

How To Get Power Query Excel

Thanks for reading.