REST APIs with PAGINATION OR TOKENS with POWER BI Reports // Beginners Guide to Power BI in 2024

in this video we're going to cover rest apis and how you can make restful calls to fetch data from the web into your parb airport we're going to briefly talk about what it is and how you can make restful calls to get data from the web as well as some other scenarios that you might encounter when using rest apis such as authenticating using tokens or dealing with pagination all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solution sub World YouTube channel where we cover tips tricks and best practices when working with powerbi upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so rest API is essentially a standard application interface that lets different computer systems interact with each other uh through the web so just think of it as a common language that is used in the web so that different computer systems can sort of understand each other so when one is asking for data or one is trying to update something they kind of have this common language that they understand what they're talking about so how it typically works is that you have your clients which sends some instructions to the server which is the server that hosts and has the data the instruction that the client sends is usually in a HTTP protocol and this can be different things it could be anything it could would be you know getting a list of users from the server or maybe updating a list in the server so the server basically responds back to this request by either giving the user or the client back the result of their query or maybe a result or maybe just a confirmation that the action was completed this is an extremely powerful tool to have especially if you're working with data just because it opens up a lot of possibilities for you in a sense that you can connect to basically any kind of computer system in the web to kind of use as part of your data model so let's start with the easiest type of rest apis which are public rest apis so these are apis that you can basically just call without needing to do anything else like authorization or you know pagination rules so let's have a look at this site rest countries. comom I'll leave a link to all of these samples that I'm using in the description box below so you can and give it a try for yourself and uh this specific website basically just uh lets you create or use restful queries to get a list of countries and their different properties so here we are looking at you know some of its documentation and its endpoints and it's a fairly to me it's a fairly simple straightforward website that you can test on how to get data using rest apis so here we have a few things here like how to use certain endpoints and uh without needing any anything at all so we're going to pick one at random here we're just going to take this one the all which basically just gets us all of the countries that is available for us to fetch so I'm going to just copy that URL I'm going to go to powerbi desktop I'm going to go to get data and then click web here it will let me paste the URL in which case uh we will just simply send the URL for this restful API if you hit okay give it a few seconds and there you go so essentially by simply just pasting that uh API it simply converted that return that it gave us as a list into something that is readable for us ready to use so a lot of these actually all of these steps were created for me automatically which sort of converts that uh resulting file from that rest call into this this file while it's looking like we can simply just use this I want to show you how that looks like without the interface that we have in powerbi so if I just uh paste that same URL into my browser this is what it returns which is essentially just a Json style file format which is then converted into a tabular format record that we have in powerbi so a lot of these things are you know properties and items which are basically just countries and parbi just makes it easier for us to work in this format so this is basically what it returns rest apis have what we call endpoints which lets you manipulates the results that your queries return so it might be adding a filter or maybe adding some values or maybe changing which Fields you bring different rest Services provide different types of endpoints that you can use which obviously differs so you typically when you're pulling from service you would check their API documentation like in this case for example we're looking at the rest countries here and we are using their API documentation as a kind of reference on how to pull the data and we just use this one the all which is basically just G gets us everything but you will notice that there are other endpoints here that you can call and it will give you different things so an easier one would be this one so this basically changes which Fields you get when you make the restful call so for example it's looking like it will only return us languages and the capitals data when we use this uh in a call so and and which is useful when you want to simply just get the data that you are using and it's typically a good way to do so if you want to keep your reports lean obviously to reduce the traffic the data traffic that you have in your data model by only bringing in the ones that you actually will be using so I'm going to copy this one and I'm going to paste it into to the browser here just to show you how it looks like again it's in this Json type format but as you notice it's a little bit smaller than the previous one because like although it's listing out all of the countries it only needs to list out a certain fields from each of those countries so let's have a look at how that looks like in parbi so let's do new source once more hit web we'll paste that end point that we just copied and we'll hit okay and uh just give it give it a second or two here we go so we still have a few columns here that I don't really want to have like for example it will just it's just giving us a lot of these other language types which we don't really want it in this format or actually we don't even want it at all so you can actually modify these end points yourself and give it the parameters that you you need so for example if we click web again and let's paste that same URL but instead let's just get the capital names because we're not really interested on the languages so I'm going to remove that from the fields part of the endpoint and hit okay okay here we are so that is a lot more usable for me because it just has the capital with all of the countries that is in this rest call so that's how we use end points with rest API so public apis as I mentioned at the beginning don't really need authentication so these apis would typically contain information information that is not confidential in usual cases your rest apis will need some sort of authentication and these are what we would call private apis so let's have a look at some examples of what private apis are and how you can use them so the one that I want to show you is from this website called open weather map which is essentially a site that let's use rest API to get the weather from or in certain locations while this is not very sensitive information they require you to use API Keys which is something that is generated as part of your account with open Weather I believe it's free at least when I use it and for testing purposes only and here we are looking at their API documentation which shows you sort of some examples of what calls you can make so here we're looking at the API call for the open weather map which the highlighted values are the values that you change yourself and the last bit here is the API key that you need to replace so it's something that is tied to your account so what I want to do is try to make this call and see if we can get this data out of open weather now in preparation for this video I've already created my account with open weather it's completely free and I've already created my API key which is what we will use today just to give you a fair warning I will share my API key as part of this demo but typically your API key is something that you would keep to yourself because it's linked to your account so when you make a call the service or the rest API service knows that it's your account that's making that call and you really don't want other people to be using your API keys to make API calls so after this demo I'm going to delete all of my API keys so you won't be able to use it but just for this demo purposes I'm going to show them to you so I'm going to copy this URL and I'm going to put it into notepad and actually maybe let's not use that one because I don't know any Lang latitude or longit so I'm going to copy this one so we can just replace the API key and then from the open Weather sorry so this is the notepad that I'm trying to paste or copy paste into let's go from this open Weather let's go to my API keys so as I mentioned I've already created my account so that means that I have this key that I can use so I simply copy this and replace this API key bit here copy the whole thing let's go back to powerbi new source and web and then let's simply paste it so if you hit okay hopefully it should return us the weather information for this geographical location that we picked so here we go so it's given us some information on that coordinates that we have copied and pasted so it gives you information like what type of weather it is over there the temperature pressure humidity basically all of the information that you would expect the rest a API to to return so this is actually a lot of really cool information and that is how you make a restful call with authentication let's have a look at another example from another website in this case we're going to cover and try to use GitHub so GitHub is essentially a website where you can store your projects as repositories and it's something that I use a lot for version controls with my parbi projects and this website allows you to essenti make restful calls I make sure of public and private apis and I just want to show you how you can generate tokens so that you can you know query GitHub through through rest apis so here we are in the rest API documentation and actually I have already a a call that I know that I can use which is to give me a list of all of my repositories that I own and the call is this one so api. github.com users / repo now with private API calls it does require you to have some authorization so again we need to generate some token or some type of token so what I'm going to do I'm going to just go to okay so um so what I've done is I've just uh recreated the token it's the the same as creating a new token essentially and it will give you this token that you can use um which uh as per the API documentation you can add it as part of your header when you make that call uh that rest API call so I've copied that token so now we're basically ready for us to start working on this restful call in powerbi so let's open up RBI desktop here once more hit new source and the open web the first thing that I'm going to do is I'm going to copy and paste this URL that we have which is essentially just the as I mentioned the the call that lets us get all the repositories that I own and then what I'm going to do is I'm going to click Advanced so the advance lets you control the URL parts of your calls so this is where you would add anything part of your header or maybe your end points and it just gives you a better interface to work with so in this case what we're going to do is we're just going to use this to add the authentication the token as part of the HTTP request header so I already know the format that we need which I believe needs to be simply just token space you paste the token there and then the the first part here will just need to be authorization something like this and if I got that correct if I hit okay here that should give me access so let me just check my spelling let me just check if I've done everything here so token authorization ah okay I think I missed the S here on our call so if I hit okay now let's have a look and see if we still get the error okay perfect so here we are so the query returns me just three rows of data which is essentially just all of the repositories that I own at least in this account there's three of them here so it's just giving me all of the information for that repository so things like followers owners which are really useful information that you wouldn't get if you didn't have the authorization to do so so the last thing that I wanted to cover is pagination which is something that you will encounter if you're pulling a lot of data from One Source not necessarily a rest API call but typically you will get this sort of limitation especially if you have results that tend to be on the larger side so when you make a restful call and the results that you're getting from the server is too big too big that it gives or it it results in some sort of service side performance issue there's typically a solution that puts a limit on how many items are in a call so instead of having a big chunk of results to give to you in one call it's separated into different pages so that it's a lot more manageable that way and that's essentially what pagination is so this means that in powerbi we need to have or add or create some sort of loop that lets you go through each of these pages and manage them individually so that we can combine them all into one table in the end so in the rest API documentation there is a a page here dedicated to pation and there's a lot of things here that if you're actually not familiar with pagination it's actually really helpful um but what I'm going to do is I'm going to try to keep it very simple because ju just to give you guys an idea but what I'm going to do is I'm going to make it a little bit easier for you guys to to understand how pagination or how we can use pagination so here is the call that I know that will work it's a public API call and as you notice there are a few endpoints that I've added here so first is it gets it gives me the list of projects that have the language of python it's giving me page one and we've also added an endpoint here so per page is equals to 10 so each page of this query will only have 10 projects so if you hit enter as you notice we don't need any authorization and it will just give you the results like this now what you'll notice here is that it gives you a total count here of how many items of results it this query returns but the thing is that uh this file itself is pretty small that's because we have this per page limit so 10 projects per query now if we want to get multiple Pages like for example more than 10 maybe we want to get 50 or something like this we will have this first call for the page one and then what we'll need to do is adjust the end point here to two so if you hit enter now will give you the next set of 10 so as you can see there is a loop that we can create that goes through the first 10 the second 10 up to how many pages that we want and I'm going to show you how you can do that all in power query so I'm going to go to Power query editor once more I'm going to create a new query here going to paste our query here hit okay okay so as you can see there's 10 items in here here um we're going to just name this into search and then what we're going to do is convert this into a function so the function we're going to add a parameter here we're going to call this page and then where we have the page part of our call where it says one we'll replace that with the parameter that will feed it so page like this so when I hit okay what you'll notice is that it Chang changes from a table into a function so now it expects you to give it a parameter for it to run so we're going to use that in a second so let's start again so let's create a new blank query like this I'm going to just create an array here so one to five if you hit enter that should hopefully give us a list of one to five right here we'll convert this into a table and then we're going to Simply click add column invoke custom function and what it will do is it will let us choose which function we want to use in which case we're going to use search and then we want to feed it the data that we have in column one which is the numbers actually I think this will fail let me just convert this into a text first so now we're going to go invoke custom function choose search once more and if you hit okay and expand these tables and there you go so what it's done is it's created a loop for each of the rows in the list of five that we have so that it gives us the 50 or five pages worth of repositories which is basically 50 and that's really it for this video so I used this same solution in a video that I created a long time ago to scrape the glass door website for reviews now that wasn't really a restful API call type service because I was basically just scraping the the front end part of their website but the solution and how it works is basically the same so if you want to learn more about how I use this Loop function in that context go check out that video thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so it do do better for next time ask your questions in the comment section box below so I can help you and you can help others if you like this video we have a patron page where you can support the channel and get the exive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye-bye

Channel: Fernan

Views: 4,336

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, power bi rest, rest api, power bi rest api, rest, restful call, rest api power bi, rest api pbi, pagination

Id: 6tid-xh1t34

Length: 20min 5sec (1205 seconds)

Published: Wed Apr 10 2024