Power BI – “POST” Web API Data Sources

Power BI POST Web API Data SourcesHave you ever tried to use a “POST” Web API as a data source in a Power BI report?

Using a POST Web API in Power BI?

Power BI makes it super simple to use a “GET” Web API directly as a data source. First you go to get data and open a “Web” data source wizard in your Power BI report. Then in the wizard you point to the Web API URL you need to get data from. Finally, you might also add a query component to the URL or an authorization header to the request.  That’s about it. Easy Peasy Lemon Squeezy, right?

But what about a “Post” Web API? After all, there’s nothing in Power BI’s “Web” data source wizard that indicates there’s a way to initiate a POST request.

Power BI makes it easy to gather data from a POST Web API as well. Easy, that is, if you have a clearly understood example of how to do it sitting in front of you. So let’s get to it!

A Power BI Data Source Example

UptimeRobot LogoThis Power BI Data Source example will use the POST Web API for UptimeRobot. UptimeRobot is a great, simple to set up service for tracking your website and server uptime. You can monitor a quite generous number of sites and have access to a variety of features with UptimeRobot’s free tier.

The UptimeRobot API Provides a variety of available methods for retrieving information about the monitors you have set up in their service. Methods for creating or modifying information in the service through the API are also available. In this example, we will be using the getMonitors method to retrieve information about UptimeRobot monitors.  We will assume that these monitors have already been set up in the UptimeRobot service.

The UptimeRobot Web API also includes many parameters to control what is done by it’s methods . We will be POST’ing 2 parameters to the Web API in this example. The first parameter, api_key, will be to authenticate with the API. The second parameter, custom_uptime_ratios, adjusts the data returned to our liking. In this case, instead of retrieving the default date range ratio for each monitor we will request ratios for the last day, the last 7 day range, and the last 30 day range.

The M Script

Let’s see what this request looks like in Power BI. First, go into edit queries in Power BI Desktop and add a new blank query. Then, open up Advanced Editor and paste the following code written in M Power Query Formula Language:

let

// The URL to the web API.
apiUrl = "https://api.uptimerobot.com/v2/getMonitors",

// The payload of authentication or other parameter/query options that you will be 'POST'ing to the web API.
// Comma delimited, ""key"":""value"" pairs.
body=
"{
    ""api_key"":""Your-Personal-Api-Key""
    ,""custom_uptime_ratios"":""1-7-30""
}",

// The "Content" option to Web.Contents is important, as it changes the web request from GET to POST and passes the POST data.
//   Note that the data sent with the content option is converted to binary to work correctly.
// The Content-Type Header is included because this particular web API can return JSON or XML depending on your requirements.
Source = Json.Document(Web.Contents(apiUrl, [ Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body)] ))

in
    Source

Results

That’s all there is to it. A few lines of code and the POST Web API request will pull your data in Power BI.  The Web.Contents function is actually the same function used by the get data Power BI wizard when creating the simpler GET Web API request.  But it’s that magic “Content” option that gives us the ability to post.

The results of many typical API’s like this one will initially be wrapped within nested levels of JSON or XML objects. Fortunately, Power BI makes it simple to point and click on objects to drill down into the data that you need.

After exploring the data within your objects and drilling down to the level you need, you’ll also use Power BI options to manipulate the data, like converting your data into table format. You can also expand a column of JSON objects into a column for each of that object’s properties. Similarly, splitting a column of comma-delimited values into individual columns is also a simple task. Continue walking through the wealth of data manipulation options available, until you have your data report-ready.

I hope you found this post useful and find ways to use something similar in your projects.  Feel free to let me know if you have any questions or comments on the material covered here.  And as a final reminder to developers, don’t leave behind a legacy of obscurity. PLEASE include comments like those included in the above code in your coding projects. That way the next poor shlub that has to maintain your old code can easily tell what’s going on.

Leave a Reply

Your email address will not be published. Required fields are marked *