How not to miss the last page when paging with Power BI and Power Query

paging pagination Power Query

When you use an API with a paging mechanism (like the example from this blogpost), you’ll might work with a field that contains the address for the next page. You can use this to walk through the available chunks until you reach the last element. That last element in the pagination will not contain a next-field or that field will be null.

Paging in Power Query

In Power Query you can use the function List.Generate for it. According the latest function documentation it:

Generates a list of values given four functions that generate the initial value initial, test against a condition condition, and if successful select the result and generate the next value next.

So an intuitive implementation would look like so:

paging pagination Power Query

Initial code for paging: Will miss the last element

In the initial step (row 2) the API will be called and returns this record:

paging pagination Power Query

Examining the result of the first call

So for the upcoming iterations (next in row 4), a reference to the field next will be made and this URL will be called.

In the condition (row 3) I say that this process shall be repeated until the next-field of my previous result ([Result]) is empty.

However, this will only return 14 list with 20 elements each, missing the last element with 13 items to retrieve the full 293 items.

Let’s check it out:

Last Element (13 rows) is missing

Solution

Honestly, I still find it difficult to understand, why this last element is missing. But fortunately there is an easy solution:

paging pagination Power Query

Split into 2 steps and reference previous URL instead

The trick lies in the adjusted condition (row 4): Instead of checking if there is a next-field in the previous record, I check if the previous record had a URL to call. That basically reaches 1 level further back and will deliver the full results.

Alternative

Actually, you can also use some “brute force” using a try – otherwise – statement like so:

Simple alternative

But this will not deliver any items for debugging if something in the calls goes wrong. So I prefer not to use try statements for looping or pagination.

Enjoy and stay queryious 😉

Comments (11) Write a comment

  1. The way documentation describes List.Generate’s third argument (the test function) can be a confusing. That argument is applied to the just-fetched result (page, in this case) to determine both whether to return the current result (page) and whether to try to fetch another result (page)—which will then be handed to the test function which will then determine whether it should be returned and another fetched, and so on….

    So, if the test function simply checks whether the current page’s next link is not null, both the current page and the attempt to fetch the next page are skipped when it is null.

    Reply

  2. Thanks for a great post. I had almost given up on this.

    One question: is it possible to change the condition to stop when the URL equals the previous URL?
    I’m working with an API that keeps returning the last page.

    Reply

    • Hi Daniel,
      yes, that’s possible. Please check the following code:

      List.Generate( () =>
      [Result = Json.Document(Web.Contents(URL)), Condition = true],
      each [Condition],
      each [
      ThisURL = [Result][next],
      Result = Json.Document(Web.Contents(ThisURL)),
      NextURL = Result[next],
      Condition = ThisURL = NextURL
      ]
      )

      /Imke

      Reply

    • Hi Sergio,
      I’m currently preparing some content to control List.Generate operations. Not sure when it comes out, though.
      There is nothing I can further do than what my mates in the forum have already tried without having access to your data, though.
      /Imke

      Reply

      • Thanks Imke. I’m not very good with M and I’ve been trying for months to look out for solutions… so it’s a bit frustrating to be hones. Anyway, thanks for your reply and for your posts. They are very useful.

        Reply

  5. Pingback: List.Generate to make API Calls in Power Query - Gorilla BI

  6. Hi Imke,

    I hope you’re well. Just wanted to let you know you inspire me. I read this article and it caught my curiosity to learn more about List.Generate. Based on the same API I extended your example with two different approaches. The article also mentions your blog examples for those who are interested.

    You can find it here: https://gorilla.bi/power-query/list-generate-api-calls/

    I would love to hear from you to know what you think of the two examples I cooked up. Is your method preferred over these two? Any performance implications?

    Thanks for sharing your knowledge.

    Regards,
    Rick

    Reply

    • Hi Rick,
      thanks for mentioning my article.
      Don’t have time currently to dig deeper into it, unfortunately.
      Cheers, Imke

      Reply

Leave a Reply