We have all been there once before. We are using a 3rd party tool and we want to extract data from it. You might have an option to export the data to a CSV or Excel file, but that requires you to be involved and can result in errors.

Next you might reach out to the support team and they send back documentation for their APIs. They think you are a programmer and probably know how to use them. But in a world full of citizen developers not everyone knows how to use these APIs to bring data into Power BI.

This series aims to demystify the process and start getting that data into Power BI and eliminate those data exports. And believe it or not, it will require a minimal amount of code to make it happen!

What is REST?

REST APIs (also known as RESTful APIs) is a specific architecture of an Application Program Interface (API). Known as the Representational State Transfer (REST), it has a standard set of rules that are widely used in the programming world. It scales well and has become a commonly accepted API that is stable. REST APIs fill a specific need to simplify integration of data between different applications.

API requests are fairly straight forward. It starts with a request from you (the client) to an API endpoint. After being authenticated, the request continues on to a database with the data you need. That data is returned back to the endpoint where it is formatted and then passed back to the client.

The flow of requests with REST APIs

For many, this flow makes a ton of sense. However, if you are not from a programming background, this can be a little intimidating. When you read API documentation, it is often full of code snippets and it can be difficult for you to get started. Sorting through these snippets and figuring out how they work can be complicated and time consuming.

But here is the good news – Power BI can handle these API requests often with no code required! The trick is understanding how REST APIs are structured and then plugging the information from the documentation into the right place.

Anatomy of REST APIs

So if we can take the documentation and plug it into Power BI, it is important to understand how REST APIs are constructed. Once we understand the anatomy of an API, it becomes easier to connect via Power BI. There are five common elements that are leveraged with REST APIs:

  • Method
  • Endpoint URL
  • Parameters
  • Headers
  • Body

You will always need a Method and Endpoint URL when using REST. However, the other elements will vary based off of the documentation. You will have to read your documentation carefully to make sure you have everything you need.

To better understand these elements, let’s take a look at them individually to understand what they do and how they work.

Method

There are four methods that are used with REST APIs: Get, Post, Put, and Delete. Methods help instruct what is happening when the API is called. For example, if you called a Delete method, you would be deleting content on the server.

Sample methods used with REST APIs

To make our lives easier, the Get method is the most common one we will use and is the easiest to implement. Post can be used sometimes, but it requires a little more effort and coding in Power Query to make it work. You will never use Put or Delete in Power BI.

Endpoint URL

The Endpoint URL determines which specific API we are calling. There will be multiple APIs available, so it is important to determine which one you need. In reviewing your documentation, you should be able to determine which endpoint contains the data you need to query.

Sample of an Endpoint URL

Parameters

Parameters filter the results of your request. Notice the “?” that separates the connection string. Before it is the Endpoint URL while after are the parameters. Multiple parameters are separated by ampersands (&) and strung together.

Sample of Parameters tacked on to an Endpoint URL

Parameters speed up requests as they reduce the amount of data requested. Often optional, but sometimes required. The API documentation will specify if any parameters are required.

Headers

Headers are designed to pass information about your request. The most common header you will need is an API key or token but you may run into others. Another common header you might need to identify is the Content-Type to specify how we want to see our response.

Sample of REST API Headers

Body

Since we are using the Get method primarily in Power BI, you will not need the body. However, it is worth mentioning it in case you use a Post method in the future. The body is another set of parameters to assist with the request.

Documentation is your friend

If you are like me then you hate reading directions. I love working through things on my own and discovering. But after many lost hours, I have learned to take a few minutes to read the documentation that comes with an API first. It takes time, but there are several reasons to take a few minutes and understand how the API functions.

Sample of REST API documentation for Yahoo! Finance

Reason #1 – Each API is like a snowflake

Every API is unique and has their own requirements. You will find some that require certain parameters to be declared. Others might need a token. I even worked with one that required a different API key for each endpoint! Understanding the components of our API will reduce headaches as we go to use them.

Reason #2 – Ensure you are receiving the right data

The best documentation is paired with a dictionary to help identify the data being returned. You may need two or three APIs to get all the information you need. Taking a few minutes to understand what data is available will help you build a plan as you build your connections.

Reason #3 – Try it out

Often APIs have an option to try them out. You can select an endpoint, fill out the parameters, and then submit the request. It brings back a sample of the data and even will assemble the connection string and headers for you. It also helps identify any required parameters and diagnose issues before you move into Power BI.

Next Steps

Now that we understand the basics around REST APIs, the next step is to transfer what we have learned into Power BI. You might be wondering why I took the time to understand the basics of REST APIs. As I mentioned before, many of these APIs can be used with no code. With a strong understanding of how they are built, we can copy/paste these elements into Power BI and start querying data. In my next post, I will show you how to connect to the Yahoo Finance API to query stock quotes in real time.

Until then, have you used any APIs in Power BI? Do you have any favorites that you like to use? Tell me in the comments below!