Stopping when looping through a paginated API feed

[RichardP]

Hi everyone,

I've been reading up on the advice for looping through a paginated API as a source (great resources here, here and here).

I have a slightly different case which I'm trying to deal with where the API I'm using will paginate to infinity producing tables with just the column headers and no data.  So the error checking is not kicking in because the pages do have a table, and the looping continues for ever!

So what I need is a check to say that if the number of rows in the table is 1 (just the headers) then stop the looping.

The function i'm using is:

= (page as number) as table =>
let
Source = Web.Page(Web.Contents("https://api.xxxx.com&max-results=10000&page-num=" & Number.ToText(page) & "&apikey=12345")),
Source1 = Source{0}[Data]
in
Source1

And the table source is:

= List.Generate( 
() => [Page Number =1,Func=null],
each (try _[Func])[HasError]=false,
each [Page Number=_[Page Number]+1,
Func=GetData([Page Number]+1)],
each _[Func])

I've looked at using Count.Rows but I'm not sure how to build it into this.  Any ideas greatfully received!

[Anonymous]

My solution:

Function "Get JSON" (which actually returns a list)

let 
    Source = (page as number) as list =>
let
    URL = Text.Combine({
        "https://some.url&page=",
        Number.ToText(page)
    }),
    Response = Web.Contents(
        URL
    ),
    Source = Json.Document(Response)
in 
    Source
in
    Source

Query

let
    Source = List.Combine(
        List.Generate(
            () => [page = 1, Func=#"Get JSON"(1)], // initial
            each List.Count([Func]) > 0, // condition
            each [
                page = [page]+1,
                Func = #"Get JSON"([page])
            ], // next
            each [Func] // selector
        )
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
    #"Converted to Table"

 It starts calling the "Get JSON" function starting with page=1 and going as long as the function returns at least 1 item.

[v-chuncz-msft]

@RichardP,

You may use the following condition to replace the try expression.

each [Page Number] = 1 or Table.RowCount([Func]) > 1,

[RichardP]

Hi @v-chuncz-msft,

Many thanks for taking the time to respond 🙂

I modified the code to:

= List.Generate( 
() => [Page Number=0, Func=null],
each [Page Number] = 1 or Table.RowCount([Func]) > 1,
each [Page Number=_[Page Number]+1,
Func=GetData([Page Number]+1)],
each _[Func])

But it gives me the error message:

Expression.Error: We cannot convert the value null to type Table.
Details:
 Value=
 Type=Type

If I try changing the line to say...

() => [Page Number=0, Func=1],

 ...then I still get the same message.

Any additional thoughts?  Many thanks for your advice.

[v-chuncz-msft · Accepted solution]

@RichardP,

Just change the condition accordingly.

each [Page Number] = 0 or

[RichardP]

Ah so obvious! 🙂

Thank you for your help.

[DBa]

Any chance you can please provide the full code? (without the actual API). Having the same challenge as well.

Thanks

[DBa]

Hi 

Any chance you can provide all the code? I'm having the same challenge as well just now.