How to Use List Generate to Make API calls in Power Query

so you want to know how to use the list generate function to make api calls in power query while these videos got you covered and the same principles i'll be teaching you can use for pagination so more and more we're working with apis an api allows you to retrieve data from certain software and every company can make their own api so it's not like a standard programming language but everybody builds their api in a way that has their own instructions i'm going to show you a way on how we can retrieve data from the pokey api so have a look at my screen here if you go to pokeyapi.com you land on the page right here and the pokey api is a database with pokemon as it suggests it does different instructions right here in how you can make api calls now i don't want to make this too difficult so let me just give you an example if we copy a piece of code here and there is an example showing exactly how we can make use of the api i can retrieve data in our browser so what's happening is i put in a certain url and the url has instructions in there so with these instructions i can give in a limit so i want to see only the first 100 pokemon and i can give it an offset so basically this api allows you to give a set of instructions just like you give a set of instructions to a function in for example power query you can adjust these variables as well so perhaps you don't want to see 100 but you change it to 10 and now you only see 10 attended this pokemon the result of this right here that's a a json document and this json file we can actually import into power query and they will rec and power query recognizes this and you can make use of that so for an example we see this first line and bulbasaur is the first pokemon it also says pokemon one then it goes to pokemon number two which is ivisar and venusaur now we're going to exploit this to see if we can make these api calls in power query and as you can see here we have a limit of 10 we can even make this a limit of 100 and you can just change this as you like one more thing i'd like to show the offset shows you if you want to retrieve the first values or if you want to skip a few so now we have the first 100 but if we put an offset of 100 right here then it skips the first 100 and then retrieves another 100. so let's make use of this so in power query to make an api call or to retrieve some value from a url you go to queries right click new query and you click on from web now this from web functionality is fantastic because you can paste the url here and power query will by itself have a quick look on what it's grasping from the internet so is it looking at sharepoint is it perhaps looking for a json document and it will try to apply uh the right function to retrieve the data now the call that we just did this is the result of that so you see all all kinds of steps have been taken and the result is the pokemon from the request i would like to go to the first step first if we have a look at this tab what you can find is that power query recognize that we have a json document first so that's this part it's a web contents request and the part right here is the url that we've input that's all that we need to make a simple request and with this request you get the results from the database the result of this request is a part here that looks like this and this is the representation of a record in power query so our record shows there is 118 pokemon in the api our current request is the one at the top and it's mentioning that our previous request was probably this here and our next request will be this one the everything that's been put down here will make it a bit easier to make the request so you can reference that if you wanted to now lastly there is the list and this list is a list of records with the pokemon that we need now one thing i want to show you before we go on is that you can reference any any of these columns within the records so let's say you want to get the the url from the previous you can have this request open a square bracket and just write the word previous and with that it will actually return you only the column that was called previous and you can do that as well for results which was the one that contained the list with all of the records that's enough for now so let me remove this now to make this interesting we are going to have to call uh we have to we have to request our api calls multiple times because for the exercise now i only want to retrieve 100 pokemon per request and i want to make those requests up until the point that there is no more data in the database so in this case we see there's 1118 but i would like power query to find it out themselves which you could in the future use for pagination as well or to not hard code any numbers anyway so to get started we delete all these steps after and we just use the template as we have it and i would like to turn this into a function because instead of putting an offset here hard coded i would like the function to be able to use a ver a value that changes so to make this into a function let's just change the query first calling it get pokemon and then you can go to the advanced editor where you see all of the code and all the way at the beginning you can write opening and closing bracket and equal and one of these bigger signs and then what i want to do is instead of hardcode the offset put a variable here now the variable in your function you can put in the front so we could write offset and then instead of hardcoding this here we will take the text we will write an ampersand and then input the uh the offset that we will have as a variable but we need to have this as text because the whole the whole url needs to be a string so you can write number to text and put the offset right there okay that's all we need by doing this our url request or our request here is turn into the function that we need let's test it so we just had an offset of something so we can we can have an offset of zero to start with now this gives us a request that has no previous but you can also change that offset to 200 for example and you will see that the list changes and you can adjust it in that way okay [Music] now things are getting interesting let's first have a look at list generate list generate is the function we're going to use to make our api calls so you can go to new query blank query and we're going to build this up from scratch so a list generate function as a first argument always needs uh an initial value the value to start the sequence with that we're going to make so we'll build up to the api but first we're going to make a sequence that starts from zero now the second argument from list generate shows that you can create a series as long as a function is the the condition in the function is satisfied so we could write each of the values we create needs to be smaller or equal to 300 now we still need to indicate what the function needs to do to create the series so we've now seen we've now seen the starting value we've seen the condition that needs to be true and as a third argument we could now write that each of the steps in the series should increase the value by one hundred there we go okay so this is very basic what list generate does so we have the value to start with which is the value here we have a value we have a condition that needs to be true so as long as this condition is true a new value is generated and the new value generated is the bottom one so each value is increased by 100 until it reaches 300. so our next step is to make sure we can use multiple columns or multiple variables for list generate and the easiest way to do that is by using records how can we transform the current series into a record with optionally multiple columns let's make a record with one single column first so our initial starting value it could be a record which we indicate by an opening record and this opening bracket could be called it includes a column called offset now instead of saying that each value needs to be smaller equal than 300 we now need to mention the the name of the of the column in the record so each offset needs to be smaller or equal to 300 and also here we're going to increase this so the offset is equal to the offset plus 100. so it's a different way of writing things but in essence this should be the same now if you look at the record we still start at zero then it goes to 100 200 and 300 notice in this example that for each of the steps we take our our column name first is written without any curly brackets so this curly bracket is only referencing the complete variable that we have here and after assigning a value to this you do have to put the curly brackets right here so it's saying our offset is the existing offset plus 100 and the next value it generates is again the offset that i created plus 100. let's continue from here so now we know how to make to use list generate to get two to get a single record here but now we wanna see two different columns for this so our offset is zero and now we wanna see that pokemon could be a value called a and we need to include that in the in the generation as well so our pokemon value equals pokemon so now when we have a look at the records that we made our first record has a value a column with the the offset value of zero and a column with a pokemon value of a and if you go down the a remains the same but the offset increases so this is let's generate with two columns now we're not looking to return the value a for each of the records instead we want to use the offset and apply it to the function that we already had and as we had looked at earlier if we have an offset of zero we can invoke the function and you can input the offset just as you like so if you make it 100 it also changes i'm quickly showing this because if we go back here we already know that the offset that we have created is 0 on the first one 100 on the next 200 and the next so what we will do now is input that value of the generated list to each to the function in each of the each of the steps so going back up here instead of referencing a we're now going to say all right i want to get the fn get pokemon function and the first time i retrieve it i want to offset it by zero steps and here we go let's put this on 100. so our first record should then show the result of our function with zero as a result now we already know how that we create an offset that increases with every step and with every step we also want the pokemon value to actually use the fn get pokemon and instead of having a hard-coded value we wanted to use the offset that increases with every step very well so we have an offset and we need to close our brackets right here so our first uh offset now uh is 100 the second one 200 and 300 but we don't have much to see on the pokemon value here instead of returning both of these columns we can also say in the fourth argument i only want to return the pokemon record so even though under the hood the offset is still used we can only return pokemon and this returns our records now let's expand this to see if things are going well because this is not everything in our data set but this is just a test so to inspect this you can click on to table okay and our first record is right there i'll click on it and then click on the list then change this again to a list and now if we just expend this to being a record so what we can see is that our first record has bulbasaur as pokemon one and it goes up to and including 100 records now if we go back here we could also have a look at the second record and we create a list and as we can see that first value in the record starts at 101. and this is exactly what we need if this is providing you any value so far like the video and we will continue with the example to see how we can build this in a more structured way as you could just see we have just created three records but the only thing that's missing now is that we have hard-coded this so we have said we only want the first 300 record uh and we if you like to you could make this the first 1500 so now this will keep on going and you see that we get all these records up to 1500 records in a row now there's something wrong as you might remember we only have 1118 records in the database so if i would click on this record right here and i would click on the list you will find that the list is empty so we made a request to get the the the 1500 to 1600 pokemon but it doesn't exist in the database so we need to do something to make sure that power query can first check whether there's any values and only if there are still values to return our request we can easily do that so so far as we build up the offset is hard-coded but we can actually check out if if our list contains any values so for that it's good to know that our record has a column called results and in the result column you find a list our next step is to make sure that this list is not empty and actually contains the pokemon and to do that we're going to change the second argument so we could write instead of seeing if the offset is here [Applause] so we could say like we want to see a list is empty and we want to check that on the pokemon record and within the pokemon record we want to check it for the results column so you open another square bracket results and instead of checking that it's empty we want to make sure that it's not empty so what have we done here so far if we just have a look at the argument right here what we're telling it to do is that you can run each of these steps so you can increment the index and make your request but only as long as the returning list in the results column from the pokemon record you can only do it as long as that list is not empty and once it's empty you stop your call now let's have a look at the returned records because this is a special moment the return amount of records is 12 of them each of those api calls can retrieve 100 pokemon and as you remember we only had 1118 available in the database so this is the result that we want power query in each step checked whether there's values in the records and in the last one right here if you click on it you can go to the list column and you find that there's 18 records and this is the last one and with that you've made your api call and got all the pokemon out of the database as you want it the index that we've used you could also imagine that this is a page because some of the pages that you request might have a page number so in my blog if you go to the second page it says the number two in the url so if you want to find all the blog posts i write you could probably use the the gorilla.bi website retrieve each of those pages and get the header titles of each of the blog posts so that works now i have a question for you guys the question of the day my question is what do you use list generate for and how has it changed your way of working what benefits does it have i have some ways that i know but i'd love to hear about yours in the comments and if this was any valuable to you make sure to subscribe to the channel to not miss out on future videos that i'll be releasing lots of power power bi and requests are also welcome as well so leave those two if you like that's all for today i hope you enjoyed the video and if there's anything you're curious about let me know in the comments too and i'll see you in the next video [Music]

Channel: BI Gorilla

Views: 34,686

Keywords: power query, power query tutorial, power query for beginners, list.generate, calendar table power query, list.generate api, power query api calls, power bi tutorial, pagination power query

Id: a_RJzoj1cnM

Length: 18min 26sec (1106 seconds)

Published: Mon Dec 13 2021