Forum Discussion

RichardP's avatar
RichardP
Helper I
8 years ago
Solved

Stopping when looping through a paginated API feed

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!

Need Help

7 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    RichardP,

     

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

    each [Page Number] = 1 or Table.RowCount([Func]) > 1,
    • RichardP's avatar
      RichardP
      Helper I
      8 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.