API loop with List.IsEmpty not working

[TrainPerson]

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.

[TrainPerson]

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.

[lbendlin]

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.

[TrainPerson]

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?

[lbendlin · Accepted solution]

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"

[TrainPerson]

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!!

[v-shex-msft]

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

[lbendlin]

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