topic Re: API loop with List.IsEmpty not working in Developer

API loop with List.IsEmpty not working

TrainPerson — Thu, 11 May 2023 13:24:55 GMT

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.

Re: API loop with List.IsEmpty not working

lbendlin — Sun, 14 May 2023 00:18:27 GMT

"List" is a reserved word. Find another name for your variable.

Re: API loop with List.IsEmpty not working

TrainPerson — Mon, 15 May 2023 07:02:45 GMT

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.

Re: API loop with List.IsEmpty not working

lbendlin — Mon, 15 May 2023 11:55:12 GMT

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.

Re: API loop with List.IsEmpty not working

TrainPerson — Mon, 15 May 2023 13:45:13 GMT

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?

Re: API loop with List.IsEmpty not working

lbendlin — Mon, 15 May 2023 14:06:32 GMT

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"

Re: API loop with List.IsEmpty not working

TrainPerson — Mon, 15 May 2023 14:41:15 GMT

Oh, I think I understand now. It keeps looping until it calls an empty page, which then shows up as an error. So that actually works perfectly, I was just very confused why the list error didn't disappear.

So basically I can go from there and remove the empty page/just expand all the records and have the full data! Thanks a lot for your help!!

Re: API loop with List.IsEmpty not working

Anonymous — Wed, 14 Jun 2023 07:45:53 GMT

Hi @TrainPerson ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng