Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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!!
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
An empty record does not equal null (see pic below). Try using Record.FieldCount([Result])>0 as your condition instead.
Pat
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
Check out the July 2025 Power BI update to learn about new features.