Forum Discussion

rsderby68's avatar
rsderby68
Resolver I
3 years ago
Solved

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

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. 

 

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

4 Replies

  • ppm1's avatar
    ppm1
    Solution Sage

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

     

     

    Pat

    • rsderby68's avatar
      rsderby68
      Resolver I

      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

  • 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's avatar
      HelpImStuck
      Frequent Visitor

      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