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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.