Getting Started with API’s in Power Query

An Application Programming Interface (API) allows a program to retrieve data from a system.

A 'program' could be something written in a language like Python or PHP, or a program could be your web browser or Excel (using Power Query).

The 'system' is any repository of data, very often web based. So you might be using an API to get data from something like Mailchimp or Paypal.

This article looks at web API's so querying such an API is like loading a web page in your browser.

There will be some technical jargon sprinkled through this blog post but the beauty of using Power Query is that you don't need to know what most of it means. Power Query hides the technicalities so you don't need to worry about doing any programming.

Watch the Video

How to Make an API Request

If you click this link Queensland Wildlife Data API you've made an API request.

By clicking the link you've asked the website des.qld.gov.au to send you some information and it has done this.

The response sent to your browser is just plain text, but it's in a specific format, JSON. Power Query understands this and can decode it.

Typically an API will return data formatted either as JSON, XML or CSV, but again you don't need to worry about this. Power Query will decode it for you.

To understand how to make an API request the first thing you need to do is look at the documentation for the API.

Let's look at an API provided by the Queensland Government. It provides data about the wildlife found here in sunny Queensland.

Queensland Wildlife API

This API is provided by the Queensland Government and allows public access to information about the wildlife and habitat found in Queensland, Australia.

https://www.data.qld.gov.au/dataset/qld-wildlife-data-api

It mentions that the data is returned in one of three formats, JSON, XML or CSV, and lower down the page the Data and Resources section provides further information on how to retrieve specific data.

Let's say we want to get all species in a particular family, I'll click on 'Get species' which takes me to a page with more information on how to query this data.

I can see that there is a mandatory variable 'family' that the query must supply, and further down the page there are examples of some queries.

So if I want to get the names of all parrots my query will be

https://apps.des.qld.gov.au/species/?op=getspecies&family=psittacidae

If I want the names of another family of animals I need to supply that family name.

I can use the Species Profile Search to see the list of species in the database.

https://apps.des.qld.gov.au/species-search/

If I want to check out the reptiles I can click on Reptilia

https://apps.des.qld.gov.au/species-search/?kingdom=animals&class=reptilia

which shows me the family names of the different reptiles.

Our youngest son has a pet bearded dragon so I'm going to look at the Agamidae family (dragon lizards).

Modifying the example query for parrots, the query I want for dragon lizards will be

https://apps.des.qld.gov.au/species/?op=getspecies&family=agamidae

To get this data into Power Query, click on From Web in the Get & Transform Data section of the Ribbon (I'm using Office 365).

Enter the query URL and when prompted for authentication, choose Anonymous.

Transform the response and save to table. You can download the query in the example workbook.

NASA API's

NASA provides several API's which are listed here https://api.nasa.gov/

To use the NASA API's you are required to generate an API key that is used in the request you make. In this case the API key is just a code that is provided in the query URL to identify you.

But if you are just making a few requests you don't need to generate your own API key, you can use the Demo Key provided by NASA, and that's what I am doing.

I'm going to check out what the weather is like on Mars. Yes there's an API for that.

Clicking on Insight: Mars Weather Service API provides more information

The InSight Mars Lander sends data to Earth about the Mars climate. At the bottom of the image above you can see the request you need to send to get this data:

https://api.nasa.gov/insight_weather/?api_key=DEMO_KEY&feedtype=json&ver=1.0

I'm only interested in the temperature so I'll discard the other data like wind direction and atmospheric pressure.

In Power Query create a new basic web query, insert the URL, choose anonymous authentication, then transform the data and load to a table.

Download this query and a reproduction of the NASA Mars Weather page in the example workbook (above).

Stripe API

If you use Stripe to take payments then you can get data about your Stripe account through their API.

Documentation for the Stripe API is here https://stripe.com/docs/api

Stripe require all requests to their API to be authenticated, so you need to get API keys from inside your Stripe account.

Stripe allows you to create test keys so that rather than using your live data, you can use test data while you get comfortable with using the API.

Let's say we want to retrieve a list of all charges (payments into your account), this is documented here: Stripe Charges

You can see that the API request uses the URL https://api.stripe.com/v1/charges so that's all we need to enter into Power Query

When prompted, choose Basic authentication, and enter your API key as the Username. No password is required.

You'll end up with data like this in Power Query.

Pagination

API's often restrict the number of records returned. In Stripe's case this limit is set to 10 by default but this can be changed: Stripe API Pagination

By adding a parameter to the URL we can tell Stripe how many records we want returned.

The documentation says that the parameter we need to specify is called limit so all we need to do to return 50 charges is to modify our previous URL to this.

https://api.stripe.com/v1/charges?limit=50

API Authentication

More often than not, API requests will require authentication. Making requests that are authenticated means your requests can be tracked. This is done to prevent abuse (making too many requests), to charge for requests, or just to count/limit requests.

There are different types of authentication.

Anonymous - no username, password or API key is required. The Queensland Wildlife API is like this.

Authentication key in request - An API key is required and is passed as part of the URL. The NASA API uses this approach.

Basic Authentication - a username and/or password is required. Either, or both, can be API keys generated from inside the API system. Stripe uses this method.

OAuth - this is a more complicated authentication process than the others and I will look to cover this in another blog post.

Summary

The hardest part is investigating the API documentation and understanding what data can be retrieved and how to do it.

But once you understand the authentication requirements and the basic structure of the query, modifying queries to retrieve different data is pretty straight forward.