Paginated Data Without Total Pages

[djs1984]

Hi,

I've been trying to fetch dynamic data from an API with no known TotalPages parameter. There is a num_pages parameter, and a has_more one; if has_more is 1, there are more pages. So, I need to keep fetching pages until has_more = 0.

I've been trying variations of this:

let
    apiKey = "{my_key}",
    fnGetPage = (pageNum as number) =>
    let
        Source = Json.Document(Web.Contents("https://}myapi}", [
            Headers = [
                #"ApiKey" = apiKey
            ],
            Query = [
                num_page = pageNum
            ]
        ])),
        Data = Source[data] 
    in
        Data,

    firstPage = Json.Document(Web.Contents("https://{myapi}", [
        Headers = [
            #"ApiKey" = apiKey
        ],
        Query = [
            num_page = 1
        ]
    ])),

    hasMore = firstPage[has_more], 

    pageList = List.Generate(
        () => 1,
        each hasMore,
        each _ + 1,
        each (pageNum as number) => 
            let
                pageData = Json.Document(Web.Contents("https://{myapi}", [
                    Headers = [
                        #"ApiKey" = apiKey
                    ],
                    Query = [
                        num_page = pageNum
                    ]
                ])),
                hasMore = pageData[has_more]
            in
                hasMore
    ),

    pageNumbers = List.Transform(pageList, each { _ }), numbers
    allPages = List.Transform(pageNumbers, each fnGetPage(_)),
    combinedTable = Table.Combine(allPages)
in
    combinedTable

but it seems wrong, and it's returning an error about the type being unable to convert number to Text.

I think I'm going about it the wrong way. Is there are obviously easier way of doing this?

[Sahir_Maharaj]

Hello @djs1984,

Can you please try this updated approach:

let
    apiKey = "{my_key}",
    baseUrl = "https://{myapi}",

    fnGetPage = (pageNum as number) =>
    let
        Source = Json.Document(Web.Contents(baseUrl, [
            Headers = [#"ApiKey" = apiKey],
            Query = [num_page = Text.From(pageNum)]
        ])),
        Data = Source[data],
        HasMore = Source[has_more]
    in
        [Data = Data, HasMore = HasMore],

    AllPages = List.Generate(
        () => [Page = 1, HasMore = true, Result = fnGetPage(1)],
        each [HasMore],
        each [Page = [Page] + 1, Result = fnGetPage([Page]), HasMore = [Result][HasMore]],
        each [Result][Data]
    ),

    CombinedTable = Table.Combine(List.Transform(AllPages, Table.FromList(_, Splitter.SplitByNothing(), {"Column1"})))
in
    CombinedTable

[djs1984]

Hi,

Thanks for responding. Unfortunately, I'm getting an error with that: