How to do Pagination on a REST API in Power Query (Part 1)

so hello everybody and welcome to another power bi video this time it's going to be about power query and we're going to get a little bit nerdy so prepare yourselves what i'm going to show you today is how you do pagination in an api rest api when you know the number of pages okay so let's get started so ladies and gentlemen first of all i want to make sure that this is understood from the beginning the different apis will work in different ways you need to read the documentation about how an api works in order to be able to implement it okay but once you know that then is this is a method for you to get all the information from an api when instead of returning one big table is returning table the big table in in chunks like 50 pages or 50 records at a time 100 records at the time it would be different depending on the api that you are requesting and the method to request the pages also will differ so this will help you with a specific case but it will give you an idea of how it works and then you will be able to do it i think with other apis too so what the api with that said the api that we're going to use today is the world bank tons and tons of good information in there and we're going to retrieve country data okay this api is not like super easy to read but here we have this is the the home page and then we're going to get country data so with all rest apis the queries that you generate or that they have on the documentation you can actually test them live which is very useful on a web browser just press the information and click enter and if you go in here and you scroll this is the result that you're going to get you scroll down you'll see that you don't get everything and already here at the top they are telling you that they are giving you page one of six pages where there are 50 records on each page and there's a total of 299 okay so we know there are six pages and what is basically this is basically giving you what it doesn't really specify as well in the api is this page equal one so they're giving you page one and then you can actually iterate through these page two page three right so it makes it actually quite easy okay so different ways to do this let me show you the first thing that we're going to do is go to copy that query and then go to power bi go to the web connector go to the web connector and we're going to paste that and that is going to grab the first information you will see it will give you the information about what the api is going to return for that table so it tells us this is a table containing the data but this is just for page one there are six pages again the same thing uh there are 50 records per page and there is 299. now there are two ways to do it the easiest way of all is you start a new query and then you go and you say hey give me you create a list right from at least from one to six because you know there are six pages you convert it to a table and put this as text you can actually do it on the fly but in case you are new what is the text here you put it as a text we do change here now you go to the first query you copy this thing the first query that you created go up here custom column and then in here instead of country you are going to do the query the other query that we did let me show you so we're going to do this square instead now because we're going to fit in this last number right so go to back to to go back to power bi and we're going to paste the page thing but instead of hardcoding the number what we're going to do is actually pass it through with column one there there we have page one but it's two page three blah blah blah beautiful and here you have all the information that you need right and then you can expand and then you'll get everything let's grab only two remove the columns and this is i mean legit obviously but apis change all the time and obviously number of countries does not change very often but if you have another api you will you don't want to hard code the number of pages to return you want the api to tell you how many pages to return so we're going to do it a little bit different let me show you so we go back to this um the first query where we just query the api and say hey tell me how many pages do i have here you have the total number of pages so we're going to drill down and grab this as a number so this step navigation gives me six as a result um we're going to go to the advanced editor for a second and give this thing a better name because that's going to be hard to write total pages done oh sorry i have to obviously do it here too total otherwise you would not know what to execute so the pages okay now beautiful and now we're going to create a new step and we're going to use a function called list numbers really nice let me show you okay so here we have list number so it generates a list of numbers the beautiful thing about these we could generate the list of the same way we did before but the beautiful thing about this is that you can actually say the increments in case of pages it was one two three it was like one fifty a hundred you could say okay one to ten by two so one three six great so it is actually a little bit more advanced just hard coding the value so we're going to use this and we're going to do list numbers and then we're going to do the first number is going to be one the so this number works first number until what number which is the total pages until six and then the increments do you wanna do it one by one ten by ten in this case we just wanted to do it one by one so you can either write one or don't write anything write one so you know that is one by one why not and it says six is yeah so here it has to be a number so let's change that to a number you can hard code it again but let's do it like that so now we get the list of one to six but this is not dynamic so if the world organization will have to add a new page our query will not break which is super nice and then you do the exact same thing you go to there [Music] close and apply now i really recommend you to test to check because you know that you were supposed to receive 299 records did you get them check it it takes a second and you are sure that you do things right right so 299. beautiful now what happens when you don't know the number of records to return when you the api does not tell you i have 30 pages or i have 100 000 rows so whatever it is that they have well things get a little bit more complex and we need to use a function called list generate so that i'm going to do for another video so this doesn't get too long let me know in the comment box if you want to have it so i'll do it sooner rather than later if you know what i mean i will do it i promise but if there's not a high request i have all the cool videos on the pipeline so let me know if you want to i will see you tomorrow on kerbal data last second channel we're going to do bar charts and you know column charts not like fun stuff otherwise i will see you on thursday as always so take care bye

Channel: Curbal

Views: 32,156

Keywords: Power bi, powerbi, Curbal, power bi desktop, power bi tutorial, power bi training, power bi for beginners, ruth pozuelo, How to do Pagination on a REST API in Power Query (Part 1), pagination power query, pagination in rest api power query, pagination in power bi

Id: g5bo_UAQjIE

Length: 9min 14sec (554 seconds)

Published: Tue Sep 07 2021