API Query looping and not stopping at null results? Help!

[rsderby68]

Hello Helpful Query Experts,

I am trying to get data from a particularly troublesome API.  It requires a POST command to get results and hides the total record information in return headers which I have not found a way for Power Query to read.  In order to get all paginated results I’m using this technique below I have had work with other API’s creating a function and then a List.Generate query. 

However, in this case it is getting all the paginated results but keeps creating an endless set of null records.  I had to stop it processing and look at the data (see below) to see what it was doing.  I don’t understand why it is not stopping when it hits null?  Can anyone help me figure out where I went wrong in the query or how to force it to stop when it receives null results? I show a postman result with "[]" which represents a null, right?  

I have been working for weeks to get data from this API and feel like I am so close! 

fTessPage function

(tpage as number)=>

let

                url = "https://tessy.com/tessituraservice/Reporting/Lists/182243/Results",

                body = "OutputSetId=1137&"&"Page="&Number.ToText(tpage),

Source = Json.Document(Web.Contents(url,[Headers = [#"Authorization"="Basic <<encoded>>", #"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(body)]))

in

    Source

Query to pull all pages

let

TessListQuery = List.Generate(()=>

[Result = try fTessPage(1) otherwise null, tpage=1],

each [Result]<>null,

each [Result = try fTessPage([tpage]+1) otherwise null, tpage=[tpage]+1],

each [Result]),

#"Converted to Table" = Table.FromList(TessListQuery, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

#"Converted to Table"

this is what postman returns when the data is empty.  This is a null, right?  

**table with all the null records it keeps collecting. 

[rsderby68 · Accepted solution]

Nevermind I figured it out.  It was actually returning an empty list {} of all things which was causing the loop.  Got it working now!!  

[HelpImStuck]

I know this is over a year old, but do you remember how you figured it out? I am having the same issue with an endless loop of empty lists

[ppm1]

An empty record does not equal null (see pic below). Try using Record.FieldCount([Result])>0 as your condition instead.

Pat

[rsderby68]

Is this what you are suggestion?  When I run this I get this error.  What am I doing wrong? 

let
Source = List.Generate(()=>
[Result = try fTessPage(1) otherwise 0, tpage=1],
each Record.FieldCount([Result]) > 0,
each [Result = try fTessPage([tpage]+1) otherwise 0, tpage=[tpage]+1], each [Result])
in
Source