Getting Started with Power Query APIs - It's surprisingly easy!

in this video I'm going to get to up and running with connecting to data sources using apis Now API stands for application programming interface and allows a program to retrieve data from a system now a program could be something written in a language like python or PHP or a program could even be your web browser or Excel using power query as I'm going to demonstrate and the system is any repository of data and very often it's web- based so you might be using an API to get data from something like MailChimp or PayPal in this video we'll look at web apis which is just like loading a web page in your browser now there will be some technical jug and sprinkle throughout but the beauty of using power query is that you don't need to know what most of it means because power query hides the technicalities so you don't need to worry about doing any programming let's get started entering a URL like this one is effectively an API request here I've asked the website dez. q.v. Au to send me some information and the response sent to my browser is just plain text but it's in adjacent format which power query understands and can decode now 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 and this page contains details about an API available from the Queensland government which provides data about the wildlife found here we can see that the data is returned in one of three formats Json XML or CSV and lower down the page in the data and resources section there's further information 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 that takes me to a page with more information on how to query this type of data I can see there's a mandatory variable called family and 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 this format if I want the names of other families I just need to supply that family name I can use the species profile search to see the list of species in the database you can see them listed here if I want to check out the reptiles I can click on reptilia our youngest son has a bit of dragon lizards so I'm going to look at the agamid family of lizards to do this I just need to modify the example query for parrots so I'll go back to the page where I had the example queries and I'll copy it contrl + C to copy and then we're going to use power query to get the data so on the data tab of the ribbon from web I'm going to paste in the example query and I'm going to replace the family name with agamid powerquery is asking me how I'd like to connect to this web page I'm going to connect anonymously there's nothing confidential on this web page so we'll simply click connect powerc has extracted a list and I'm going to convert it into a table and then from there I can expand the list to new rows if I click in the records you can see the information that's available for each record these are all the columns and I can expand those records and I can deselect any columns that I don't want let's load them all for now we'll take a look and here's the data about the agamid family of dragon lizards if you look here there's further records I can expand on so for example I can look at the NCA status which tells me whether the species is endangered vulnerable or there's no concern so let's look at endangered and vulnerable only and we're left with two species that have that criteria and I can simply close and load and here I can either close and load to a table a pivot table report pivot chart just create a connection or I can even add it to the data model which is power pivot let's just load it to a table in the worksheet for now the nice thing about having an API connection is it can be refreshed at any time simply right click and then refresh the query and that will give you the latest data NASA provides several apis which are listed at api. nasa.gov to use the NASA apis you're required to generate an API key that's used in the request that you make in this case the API key is just a code that's included in the qu query URL to identify you but if you're just making a few requests you don't need to generate your own API key you can use the demo key provided by NASA in their examples and that's what I'll be using so let's check out what the weather is like on Mars yes there's even an API for that if we click on the plus beside Insight here it tells us that the Insight Mars Lander sends data to Earth about Mars's climate and below the image you can see the request you need to send to get this data so I'm going to copy the URL here and we'll go back to Power query data from web I'll paste in the URL again it's asking me how I want to authenticate I'm going to use anonymous there's nothing confidential here this API provides P summary data for each of the last seven available souls and a soul is a Martian day the last two rows are just metadata so let's convert this into a table and I'm going to filter out the metadata in the last two rows and here we have the last seven so numbers if we click in record you can see there's further information so let's expand this and again we can drill down even further at is atmospheric temperature hws is horizontal wind speed p is atmospheric pressure and WD is wind direction now I'm only interested in the weather so I'm just going to delete these pressing the delete key and let's expand the records for the atmospheric temperature and there they are now I'll let you play around with it and read through the API if you want further understanding of this data for now I'm just going to close and load and it's going to load it into a table which is my default setting let's take a look at another example if you use stripe to take payments then you can get data about your stripe account through their API at this URL stripe require all requests to their API to be authenticated so you need to get API keys from inside your stripe account in the meantime you can create test keys so that rather than using your own live data you can use test data while you get comfortable with the API now let's say I want to retrieve a list of all charges which are payments into your account you'll find the documentation under core resources charges and then list all charges you can see that the API request uses a URL and I'll just copy it here and we'll go back to Excel data from web I paste it in now remember stripe requires a username and password so I'm going to go in and use basic authentication and I'll go back to the stripe page I'm going to copy the username this is just the test username and I don't want the colon on the end we'll paste in the username and simply click connect there's no password for this account this is just a test account but you will require a password when you connect to your own stripe account so again I'm going to convert this into a table I'm only interested in the data so let's filter out everything about the data then I can expand to new rows and expand the record cords and there we have some sample data relating to charges to the test stripe account and again we can expand the records even further if required you can see there's a few columns with further records so you can see that connecting to apis with power query is relatively easy especially if the API is well documented I hope you're excited to try our apis you can download the Excel file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for [Music] watching

Channel: MyOnlineTrainingHub

Views: 114,207

Keywords: power query api, get data from api, api excel, api power bi, power bi api, excel api

Id: 2mR44X7PrRo

Length: 9min 17sec (557 seconds)

Published: Wed Oct 07 2020