Automatic pagination with incrementing offset

[hexitated]

Hi, I have a problem with my data source.

I am using an external API which only allows me to show 100-200 projects at once.

Unfortunately it will not show me the total pages. I hoped that this thread might be able to solve my problem

but I guess it does not because of this problem..

https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi

So I always need to put a limit in my request in order to see projects. Is it possible

to create the query with a dynamic url? So basically it should increase my offset by 100 as long as my itemCount is 100 with a constant limit of 100 for example.

As soon as my itemCount goes below 100 it obviously reached my last page (we have around 10 in total).

I hope you guys get what I mean. So far I overcame this issue by hardcoding it and then merging all of my single requests to one. But this leads to me always having to make sure that I am not missing out a new page.

Thanks everyone!!

The API endpoint only delivers my project data as well as "itemCount", "limit" and "offset"

[darshanas]

@Thex You can use a Do Until loop in Power Automate for pagination if you can create a connection to your data source in Power Automate.

• Use 'Initialise Variable', name it Offset, select Integer and set the initial value to 0

• Create a Do Until loop with the Offset created above equal to the largest number possible (since you do not know the total pages

•  Inside the Do Until loop, use ‘List Records’ to list the records you want from your data source. Here, use limit as 100 and offset as the Offset variable created above.
• You can add other actions within the Do Until loop such as storing these projects to your desired location for example, a SQL server or a database
• For the last step within the Do Until loop, use ‘Increment Variable’. Select Offset and the increment value as 100

• Run the flow

This loop will first get 100 records, then the next 100 records and so on until the largest number you have passed is met.

Hope this helps anybody facing a similar issue.

[V-pazhen-msft · Accepted solution]

@hexitated 

Please review the following blog to implement REST API pagination in Power Query.
https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[hexitated]

Thanks! Even though the article did not help for my exact problem I could use it as a start to build my own functions. Since my API does not show my the maximum entries with one request I had to build a function which call itself until the point where my itemcount is below 100 which means that I reached my final page. After lots of trial and error it seems to work now. I'd like to share my code and get some feedback if I did everything right. 

let
    BaseUrl = "xx",
    Token   = "xxx",
    Offset = 0,

    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,

    GetEntityCount = (x) =>
        let Url = BaseUrl & Text.From(x),
        Json = GetJson(Url),
        Count = Json[#"itemCount"]
        in Count,

    GetPage = (x) =>
        let Offset = Text.From(Offset + x),
            Url   = BaseUrl & Offset,
            Json  = GetJson(Url),
            Value = Json[#"data"]
        in  Value,

    CheckCount = (x as number) =>
        let Indices =
            if GetEntityCount(x) < 100 
            then x 
            else @CheckCount (x+100)
        in Indices,
    
    PageIndices = { 0 .. CheckCount(0)/100 },
    Pages       = List.Transform(PageIndices, each GetPage(_*100)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
    Table

So the return is the exact 1698 Projects which is up to the offset=1700 endpoint. I am just wondering why I am also getting all projects when I start with:

PageIndices = { 0 .. CheckCount(1500)/100 }

Shouldn't it just return 298 Projects then since I start with offset 1500?

Best regards!

[hexitated]

Nobody here that might has an idea? Or is there not really a way to solve this?