From the course: Power BI Data Methods

Connecting to APIs - Power BI Tutorial

From the course: Power BI Data Methods

Start my 1-month free trial Buy for my team

Connecting to APIs

“

- [Instructor] An API or application programming interface is how computer applications talk to one another. They enable us to query data or subsets of it. One way we can use APIs within Power BI is to get data to import into our model. We can then set up regular, frequent data refresh schedules to this API to ensure that we have the latest data measurements. Each API is different, but to build an API request, we need to figure out the required components, including the endpoint, the parameters, and the key or token. Other than the endpoints, the parameters and key or token are sometimes optional. We're not going to get into building APIs here, but it's useful to know. We can get the latest hourly weather data from the Aviation Weather Portal available through the National Weather Service. One of these data sets is the METAR data set, which gives us hourly measurements of the data. The required components that we need to build the API with are available on its documentation page like we see here. The fantastic thing about this portal is that it has an interface where we can input and test our parameters, and it will give us the complete API query URL we need to use to get that data in PowerBI. Let's query the METAR data by expanding this section on the page. We need inputs to configure our request parameters. However, to access them, we need to enable that option first by clicking on the Try it out button in the top right. Let's then choose the single weather station ID. I know that I'm using Santa Barbara as my station ID, which has the code KSBA. If you want to look at another weather station, you can find it by searching the National Weather Service. Some APIs like this one give us the option of the format of the results we want to return, which includes raw, JSON, and so on. I've used this API before, and I found that raw data, which returns text format data, most consistently works if we want to update the data frequently. Let's set the TAF parameter to false. We'll then input 1000 hours as the time periods we want to go back in the historical pass for data. We'll leave the rest of the query parameter inputs blank. To see what the API request query will look like, let's choose to execute a query. We see what our API results look like if we scroll down. We see what our API results look like as a series of text strings, where each measurement time appears as a single row, followed by the data associated with that date and time, which is in UTC time. If we want to further continue to refine our API here, we can do so by testing it out. We can see the get request with this API query once we execute it. Let's copy this get request URL query string so we can use it in Power BI. In our web connection option, let's copy and paste the URL string from the API testing tool, into our URL input parameter. Once we confirm our web connection, we see the results. Notice the Power Query split it into three columns. This works because the station ID is four characters, and the UTC date time always has the same number of positions within it. The rest of the data string is not necessarily the same for each measurement, but we see that reflected all in column three. We'll continue to transform and work with this query throughout the course. Let's rename it METAR API. When you're connecting to an API, the documentation and examples are incredibly helpful references to use to develop the query. I find that working with APIs require both skill and also practice and tenacity to figure out what's going on. If it doesn't work at first, don't be deterred, and you can try again until it does get the results you want. We can set up API connections in many ways, and this query is one example of that.

Contents