Forum Discussion
API loop with List.IsEmpty not working
I am trying to call a REST API with a POST request. There is a limit of 1000 entries per page and it is unclear how many pages there are. I found a sample solution that seems to fit, which is to call every page until they come back empty. However the script is not quite working for me.
It works when I hardcode the page limit (iterating from page 0 to 10) like this:
= List.Generate(
() => [offset = 0, List = #"API Query (test)" (0) ],
each [offset] = 10,
each [offset = [offset] + 1, List = #"API Query (test)" ( [offset] ) ],
each [List]
)
however when I try to give a dynamic limit like this:
= List.Generate(
() => [offset = 0, List = #"API Query (test)" (0) ],
each not List.IsEmpty( [List] [results] ),
each [offset = [offset] + 1, List = #"API Query (test)" ( [offset] ) ],
each [List]
)
I get the following error:
The API topic is completely new to me, so even after some research I'm coming up empty on ideas what could be the issue.
Thanks a lot in advance to anyone with ideas.
Seems to work fine here
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), APIQuery = (n)=> if n<6 then {0..n} else {}, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> List.Generate( () => [offset = 0, List = APIQuery(0) ], each not List.IsEmpty( [List]), each [offset = [offset] + 1, List = APIQuery( [offset] ) ], each [List] )), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Expanded Custom1" = Table.ExpandListColumn(#"Expanded Custom", "Custom") in #"Expanded Custom1"
7 Replies
"List" is a reserved word. Find another name for your variable.
Hi, thanks for your reply! Unfortunately, I tried that and it did not change the error. 😞 It ist literally exactly the same error message as before.
Lists are not records. They only have indexes, so you can address their rows. In other words, list have only one column. So
each not List.IsEmpty( [List] [results] )won't work.
Instead you can use
each not List.IsEmpty( [List])or equivalent.
Hmm... ok. So this worked, but not as intended. It also shows and error message, but weirdly behind the error message are just records. It gives me back the first 1000 records (max records available per page) of the first page, but nothing beyond that, as if the looping isn't continuing?
