82 Importing Data from REST APIs | Other Data Sources | Power BI

in the last lecture we had a look at how we could import and use JSON data in our project now power bi project and it was a bit difficult because we had to drill down into the data structure and make sure we transform it into a two-dimensional table we can actually work with in this lecture now I want to take it to the next level and whilst people in the end still use JSON data I want to show you how to get this data by directly querying a restful api service and which better service to query then this Star Wars API so feel free to Google for Star Wars API so that you can wizard swappi dot CEO and here you'll find an explanation on how it works in what you might query and I'll show it here on your web page you can simply for example query all the people so the full URL would be HTTP swap adagio slash API slash people for example and if we send this request we would get back JSON data which looks like this so object which has the overall count of people and then the results which is a list of the individual characters we have the Starcraft movies so that's really interesting I hope so let's copy that URL here the API part at least we'll have to add people manually and let's go back to power bi in there we can click get data and then we can click web or of course all done more here at the bottom web and that's also how you could import full web pages here however we're just getting chased in there that RBI will automatically find out which data it receives and handle that correctly so here we can enter the URL don't forget to add people or whatever you plan on querying at the end and then we can also switch to advanced now what can you do here under advanced we get the URL parts for example and this would allow you to structure your URL so you could either enter the full URL or you could alt enter people here and that should also create a URL of all these parts so that is how you can also query that so let's use this way for examples sake you'll see the full URL preview down here you can specify any time out if you have some service which might take a bit longer you could specify that after one minute of inactivity so after one minute of not getting any result you want to quit so that power bi does keep on querying that and doesn't wait for a response here I'll leave this empty which means wait until the data is there and you can also set some custom headers now maybe you have a service where you need to pass some authorization header or some other header to have it work correctly and have it return the correct data you could set these HTTP headers there and you set typical HTTP headers for me I don't need it here so let's just hit OK to query that API as it is and we get back JSON data as you already knew it now let's transform this into a table to begin with and then we can again drill down into the chase data for example into that list here now where is that expand item we were looking for if we have a look at this we can't click it why because only one of the items here is actually a list so if we wanted to transform this we would have to do it differently here we could drill into the list here and convert this to a table which now if we click OK we'll actually overwrite our people query so that it's no longer our original data but just that list and of course you could have saved us as a new query if you want to keep both now with this we got our column and now we can split this and now you see we have all these fields named height mass and so on for all the characters and you scroll over you see you could drill down even more into a list of films and so on now with that data being loaded like this you could drill down further into it and transform it however you want to transform it to get the analysis you want to get I think it became clear how you can connect your HTTP service get the data the service returns and of course this doesn't have to be Jason that's all important power bi is not only able to of Jason early and of course you already saw how it works with a web page which loads HTML here you see it work with Jason and if dad would return CSV that works too basically and that's a key takeaway the web data source is like a wrapper around other data sources which you can't handle in power bi - like csv text files whatever it is and whatever your service here returns power bi will then be able to handle it

Channel: Speedilytech Team

Views: 111,938

Keywords:

Id: hhjOHYIeHuY

Length: 5min 1sec (301 seconds)

Published: Tue Sep 24 2019