Pagination and DO/WHILE in Power BI / Power Query

[@JacktheYeti3]

The most articulate and thorough answer I've found regarding API pagination using PowerQuery. Thanks so much!

[@edwardwashington503]

Way better than Microsoft's helper Function :) Thank you! 3 years later and it still blowing heads!

[@damianspencer]

This was the most helpful tutorial I have found online. Thank you!

[@Mahmoudalgindy]

This is unbelievable, Wow the best trick I have seen ever, Really thanks so much.

[@krunalpatel9571]

Thank you so much ..this is really helpful and this is very useful for my use case, where i have 15000+ records and client support 100 records per API call.

[@arunsahoo1277]

Sir, could you please tell me after expanding those 15,000+ records you must have millions of rows. How did you load all the data to Power BI. Didn't you get any 429 error (Too many requests) or from Power BI rows limitations.

[@Broadmoore]

AHHHH!!!! What’s your Venmo, I owe you 😂. But seriously… this is amazing thank you

[@DaveSlavens]

Thanks for this video! It really helped me with getting information from a Web API with 70,000+ records.

[@ThePowerUser]

Hola Dave! sorry to ask, but how long did it take you to load all of those records?

[@arunsahoo1277]

Sir, could you please tell me after expanding those 70,000+ records you must have millions of rows. How did you load all the data to Power BI. Didn't you get any 429 error (Too many requests) or from Power BI rows limitations.

[@DaveSlavens]

@@ThePowerUser I just saw this today... It took a few hours.

[@DaveSlavens]

@@arunsahoo1277 No errors since the API I was calling only allows 500 requests at a time and I had to loop through calling the API using the instructions in this video.

[@arunsahoo1277]

@@DaveSlavens In my case I am using SurveyMonkey API. Just curious will this technique will work other than WebPages.

[@ephraimkizito8094]

Thanks Miguel, what would the GetData function look like if using Json.Document rather than Web.page?

[@ThePowerUser]

that would depend on what the GetData function should output, but the reality is that the GetData function could be anything. Conceptually is all about using the "GetData" to read a "page" and using the List.Generate to move through the "pages" until you finish the whole "book"

[@frankradewagen3488]

Thank you very much for this amazing Solution, which might help to resolve my Problem. I am trying to Loop through a SharePoint List with projects, referred to as the Overview. One column holds the name of the corresponding subsite with more details. Each subsite has a „Financials“ List which data should be gathered. The resulting list should have the project Name with some columns from the Overview plus the Financial details from each project subsite. Resolving this problem would be a life saver for me. Any idea or support for getting this done is therefore highly appreciated. Thanks in advance and looking forward to more of your phantastic tutorials.

[@ThePowerUser]

Hey Frank, I'd highly recommend that you post your scenario (with pictures if your dataset if you can) on the official Power Query forum: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery I don't think you need a looping solution, but rather just a function that works on a row by row basis, but I'd still need to see your data and what you're trying to do with it.

[@frankradewagen3488]

Hi Miguel, thanks for the tipp. Just posted the question on the forum. Unfortunately I was not allowed to post pictures or hyperlink unless my account has been verified. So I tried to illustrate it the old fashioned way, hoping that this helps to illustrate the scenario.

[@mariiazh544]

Hi @The Power User I want to return the [Result] and the page number. Do you think it`s possible ?

[@SamVeneman]

How would you do this with an API that has an endCursor (not an actual page count but a parameter you pass to get to the next page)?

[@ThePowerUser]

this article might help you --> docs.microsoft.com/en-us/power-query/handlingpaging

[@igordemetriusalencar5861]

Man, that is what exactly I was looking for.

[@readwithfatima7869]

Hi, its not generating anything for me.. how it worked for you?

[@donald-parker]

Good job! You really need to think about problems a different way with M.

[@ThePowerUser]

You bet! To be completely honest, I feel like this is an amazing language for someone who knows about json and DAX is pretty nice (at first) for people who know Excel.

[@jackrush112]

Could this be adapter to work on a cursor based pagination? How would I go about that?

[@ThePowerUser]

you could implement your own logic using List.Generate and effectively storing the cursor for the next page in a field and then using that value in subsequent calls. Completely doable

[@777tmack]

The last question.... can the query include multiple years and all associated pages in a single query?

[@ThePowerUser]

is completely up to you. List.Generate can create a list with all the data that you want. It's the perfect solution for pagination

[@MBBajesh]

Hi, thanks the video. Need some help here. I have an rest endpoint with a single parameter as scroll_id. This endpoint has to refreshed N number of times to fetch the complete dataset. Help will be much appreciated. Thanks

[@ThePowerUser]

hey! I'd recommend posting your question (with as many details as possible) on the Power BI Community forum: community.powerbi.com/ Usually when dealing with REST APIs it's 80% understanding your REST API and then 20% actually translating the logic into M code, so I'd encourage you to post a link to the official documentation of your REST API - I believe you might be referring to a "pagination" mechanisms, but if it's not and you're effectively talking about "refreshing" the same endpoint N number of times then that might be something quite different to what I showcased in this video. Best!

[@mshparber]

Great! What about next page url returned by a response header? like in okta api? how can PQ catch it?

[@ThePowerUser]

by design, you can't access the response header in Power Query. You'll need to create a Power BI Custom Connector. Learn more about them from here: github.com/microsoft/DataConnectors/

[@keithchai8566]

Awesome video. Thanks for sharing 👍 god bless u for the amazing content.

[@larschristiansen7695]

Great video! Can this approach also be used with a Rest API with top=x and skip=y instead of pages?

[@ThePowerUser]

hey! from a purely conceptual standpoint List.Generate would be the function for the job, but the logic for how to paginate to that specific API would need to be adjusted accordingly

[@Bobbyfracselrod]

I've got a query that returns 5K records at a time with a "next_page" value nested outside of each call. In order to loop through the data that "next_page" value has to be fed to the end of the query (ie. "data?next=next_page"). How do I create a table/list/variable that I can update in this loop instead of having to generate a list? Biggest issue here is I don't want to have to physically create a list for it to reference in the loop, I'd much rather it do it dynamically as the "next_page" value is passed in. Is this possible?

[@ThePowerUser]

Hey! I'd highly recommend that you check out this article from Microsoft: docs.microsoft.com/en-us/power-query/samples/trippin/5-paging/readme These type of scenarios fall under Power BI Custom Connectors territory, so it's a great idea to see how this is done at a Custom Connector level.

[@yuriabreu8791]

Thanks a bunch for ths video. You're a lifesaver! This helped me a lot and is just what I needed.

[@10ozGold]

Thank-you! Very good video. I wanted to make the hard-coded year 2013 to be dynamic by declaring the year as a parameter. GetData function (page as number, year as number) => within source, changed 2013 to ... & Number.ToText (year) & .... What's the code to update within List.Generate?

[@ThePowerUser]

hey! you'd just need to update the instances where you use the GetData function within List.Generate, so you'd end up with something like: GetData(page,2013) if you added that new year parameter. Again, you'd need to make that change for every instance where the GetData function is being used so you can define what year it should be paginating through

[@dieter9324]

Thank you! It works! Just one question: I'm getting data from an API and, if data is not found, a record is returned with {status: "FALSE", message: "There is not data'}. Since is not null it keeps bringing me data forever. How can I modify the condition just to make it stop when status is equal to False? Thank you.

[@ThePowerUser]

Hey! you can modify that part of the code however you want to define your "loop" to work. I also highly recommend that you post your full scenario on the official power query forum here: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@dieter9324]

@@ThePowerUser Thank you. I have just posted my question there. Thanks

[@therewego7881]

Hey, I don't know if you still reply to comments on old videos, but thanks so much for this code! It's really helped me out, however I am having a bit of a problem with it. It does work for my data, and pulls out all the pages that I need, however it took a veeery long time to run the query and, when I did finally get results, it made extra, blank records all the way up to 1000 (only the first 6 records actually have my data in them). Do you have any ideas what I might have done wrong?

[@ThePowerUser]

Hey! it really depends on your data source at this point. You might need to add a piece of code to determine when to stop, like evaluating a piece of the page to see if it contains any data and if it doesn't, then stop at that point. I encourage you to post your full scenario on the official Power Query forum here: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@therewego7881]

@@ThePowerUser Will do! Thanks for the answer.

[@matheusacx]

Amazing solution ! Thank you for sharing this

[@matthewglidewell5210]

What if my Source needs to equal Json.Document instead of Web.Page, would that change the Query? When I use Json.Document as my source instead of Web.Page, everything works but I receive no records, just a list w/ the word list. I was expecting SurveyMonkey responses. Thank you.

[@ThePowerUser]

it depends! It's tough to give you a good suggestion without looking at your code first. I could be pointing you in the wrong direction and make you lose hours of your time. Json.Document requires a binary as its first argument, so I'm guessing that you're using the Web.Contents to get the binary from the web? (perhaps a REST API?) I highly encourage you to post your full scenario and code on the official Power Query forum here: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@matthewglidewell5210]

@@ThePowerUser Correct, a REST API from SurveyMonkey. I can get a single page of requests by querying a specific page, but when I attempt to replicate this video I get no results. I assume because the JSON results are nested.

[@ThePowerUser]

@@matthewglidewell5210 while the concept is similar, the specific code shown in this video is specific to this webpage. For other data sources different from webpages, such as a rest API, you'll need to developr your own GetData function and even might have to create your own paging function using List.Generate. Here's also a helpful article from the Custom Connectors SDK github.com/microsoft/DataConnectors/blob/master/docs/helper-functions.md#tablegeneratebypage

[@Theman-jv2qn]

Thanks Miguel, after viewing this a few times...I think I can do while...lol great work

[@mattyjay1]

I have adapted this for a web api. I am running into trouble in the query end. My api returns lists which hold son records. There should only be 4 lists with content but it pulls 999+ rows. The extra lists are blank. As this works if error then add null. Is there a way to do a similar thing that if a list is returned and is empty stop?

[@ThePowerUser]

hey! you'd need to check your list with something like List.IsEmpty and adapt the code appropiately

[@mariiazh544]

List.IsEmpty([Result]) = Logical.From("FALSE") had the same "issue". that works perfectly for me :)

[@kallmer]

@@mariiazh544 Hey! Where did you place this code? I´m having the same issue

[@mariiazh544]

what a great video! thank you for the tip! but unfortunately, it doesn`t work correctly for me. At first I`m getting some results which is good :) but then there`re just nulls so it looks like the condition while result null doesn`t work. do you have any ideas about that?

[@ThePowerUser]

hey! have you checked if they Try ... otherwise null is stated in your code? I haven't tested the code for this website, but conceptually the null should work as long as the GetData function returns an error that gets translated into a null (using the try otherwise statement)

[@mariiazh544]

@@ThePowerUser Hi, thank you very much for your reply and for your time :) I did some investigations and found out that With my data (I took data not from the Box Office mojo) I`m generating a list (with your function). And this list contains lists (so it`s list of lists). And obviously the result(in my case a list) cannot be null. So I had to add a check if the List is empty and it works just perfectly now. Again, thanks for your video it was really helpful!

[@arunsahoo1277]

Sir, My list of ID's that require tables are already dynamic. So, every time a new ID is created that will invoke the transformed data table from the function and will store there. The Problem I am facing now is, after expanding the table I now have more than 2Million rows and I can't load all the data to Power BI interface as I am getting 429 Error (Too Many requests). Also please note I have a limitation of 500 API calls per day and don't have Power BI premium service. Please suggest a workaround.

[@ThePowerUser]

Create a Power BI Custom Connector. That's the best way to go.

[@777tmack]

Great Video per usual. I'm sure I'm missing something obvious, but any idea why the code below would generate error code Expressions.SyntaxError:Token Comma Expected =List.Generate( ()=> [Result= Try GetData (1) Otherwise null, Page = 1], each [Result] null, each [Result = Try GetData([Page]+1) Otherwise null, Page = [Page]+1], each [Result]) I used above in conjunction with Matt's GetData function.

[@ThePowerUser]

hey thanks! my bet is that the Try needs to be replaced with try (all lowercase). Let me know if that fixed it

[@777tmack]

The Try and Otherwise all lower case did the trick. Thanks again for this great video!

[@josephansah]

Hi @The Power User The page on Matt's blog which you link to no longer works. Clearly, this video is a classic from decades ago, but can you kindly paste or link to the code to create the GetData function if possible? Otherwise, a novice user is partially led to resolution paradise.

[@ThePowerUser]

hey! you are absolutely right. It appears that the page from Matt is no longer working. Unfortunately, I don't own that page nor the BoxOfficemojo one which appears to have changed as well and I don't have a copy of the contents that were in those pages either. Nevertheless, from a purely conceptual standpoint, the video is still valid. If you do require further help in terms of how to create your own pagination function, here's an official article from the Power Query team around this topic and how it can be used even within Power BI Custom Connectors: docs.microsoft.com/en-us/power-query/handlingpaging Hope this helps!

[@josephansah]

@@ThePowerUser Thanks for the response and the link.

[@viniciuskostriuba853]

Hi, I wanna do it with JSON API, can you help with a video? Thanks

[@ThePowerUser]

Hey - For the next few months I'll be focusing on my blog www.poweredsolutions.co/blog/ with a weekly blog post. I'll take your request in consideration for a future blog post, but in the event that you need immediate samples, you can check these sample codes: github.com/Microsoft/DataConnectors/tree/master/samples/TripPin/5-Paging github.com/migueesc123/WooCommerceConnector/blob/master/Woocommerce/Woocommerce.pq github.com/migueesc123/MeetupConnector/blob/master/Meetup/Meetup.pq

[@asif.bhatti]

Thanks for this! Worked well.

[@RusinhoMV]

¿Buenos días cómo estás? El video es genial, estoy buscando hace un tiempo la forma de hacer una consulta, tomando un parámetro de otra consulta, porque tengo que levantar muchas tablas de diferentes directorios, ¿podrían echarme una mano? Gracias

[@ThePowerUser]

Hola! Lamentablemente la sección de comentarios de KZbin no ha sido diseñada como otros foros, por lo que te recomiendo publicar tu escenario completo con archivos e imágenes de ejemplo en el foro oficial de Microsoft: community.powerbi.com/t5/Translated-Spanish-Desktop/bd-p/pbi_spanish_desktop

[@RusinhoMV]

@@ThePowerUser Thank you very much for your answer, I did it a long time ago but I have not found an answer until now, I don't know if it is too complicated what I am trying to do community.powerbi.com/t5/Translated-Spanish-Desktop/Consulta-masiva-dbf/td-p/1120342

[@georgekaroubas6136]

Thank you for this Great Video

[@ThePowerUser]

thank you for watching the vid!

[@NorbertoVeraReatigaNVR]

Muy buen video, Gracias!!!

[@ThePowerUser]

gracias a ti, Norberto!

[@SustainaBIT]

GENIUS

[@pablovalverdesoria1470]

can anybody send the code?

[@arekbombka9750]

Awesome!

[@ThePowerUser]

hey! thanks for the comment. Hope you enjoyed the video :)

[@salvadorrequenasilla6553]

Thanks a lot!!!

[@uighur4ever487]

The fuckin video is loaded by 1-3 min long ads!!!!

[@malosephaka9069]

Hi I'm stuck with this problem, I am querying an API that only allows you to bring back 10 records at a time. Then you have to do another call and provide the next page key to get the next batch of 10 records and so forth. Is there a way for Power BI to use a variable to pull in the next page key from the response, and then use it in the API call to cycle through the data till the end of the data.

[@ThePowerUser]

hey, yes. The pattern showcased in this video should help you as you can create new fields that will store the data that you need to construct your url

[@uniQue_XL]

Thanks a lot!!