Excel Web Services and POST Requests

Overview

Excel Get and Transform (Power Query) and Microsoft Power BI provide data analysts with flexible data connection and transformation options in a familiar environment. The purpose of this post is to demonstrate a less common approach to accessing data from a REST API with Excel and Power BI. While this post isn’t for the Excel beginner, it doesn’t require deep development knowledge. Any tech savvy user with some knowledge of web data sources and Excel should be able to follow this quick guide.

By default Web.Contents() generates a GET request, which works great for most scenarios (e.g., getting data from a WordPress site). But what if you need to make a POST request? By combining Web.Contents() with the Content option, you can also make POST requests. It’s not well documented so here’s a quick rundown of how you can accomplish this using a real-world example: getting daily report data from Zoom REST API.

Zoom is a good example because it only uses POST requests, which forces us to use Content in Web.Contents() request in Excel.

First, I would recommend scanning these two relevant resources:

1. Chris Webb’s post on working with Web Services and Power Query

2. This thread on posting XML to a RESTful service

Note: You will need a Power BI Pro account if you are interested in actually using Zoom for this example. You can find the Zoom REST API docs here.

How It Works in Excel

1. Open Excel
2. Click on Data in the ribbon
3. Select Get Data > From Other Sources > Blank Query
4. Click on Advanced Editor
5. Enter your POST request, which could be simple like this query string

= Json.Document
(Web.Contents("https://api.zoom.us/v1/report/getdailyreport?api_key
=apikeywouldgohere&api_secret=apisecretwouldgohere&year=2017&
month=8", [Content=Text.ToBinary("content")]))

Or more complex
let
APIURL="https://api.zoom.us/v1/report/getdailyreport",
APIKey = "apikeyhere",
APISecret = "apisecrethere",
YearValue = "2017",
MonthValue = "5",
PostContent ="",
StringToUse = "api_key=" & APIKey & "&api_secret=" & APISecret & "&year=" & YearValue & "&month=" & MonthValue,
FullURL = APIURL & "?" & StringToUse,
Source = Json.Document(Web.Contents(FullURL, [Content=Text.ToBinary(PostContent)])),
dates = Source[dates],
ConvertedToTable = Table.FromList(dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"date", "new_user", "meetings", "participants", "meeting_minutes"}, {"date", "new_user", "meetings", "participants", "meeting_minutes"})
in
ExpandedColumn1

Breaking it Down

The essential parts of the above script is

Json.Document(Web.Contents(url, [Content=Text.ToBinary(content)]))

Web.Contents() alone will attempt to GET from the endpoint you provide. By adding Content after the url, you are telling Excel to make a POST request. This is the only way to make a POST request in Excel at the moment for this type of data source. Hopefully this saves someone time.