Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rsderby68
Resolver I
Resolver I

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?  

 

rsderby68_1-1668283899013.png

 

 

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

rsderby68_0-1668283804710.png

 

1 ACCEPTED SOLUTION
rsderby68
Resolver I
Resolver I

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

View solution in original post

4 REPLIES 4
rsderby68
Resolver I
Resolver I

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

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
Solution Sage
Solution Sage

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

 

ppm1_0-1668294163928.png

 

Pat

Microsoft Employee

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

 

rsderby68_0-1668295124013.png

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.