Forum Discussion

TrainPerson's avatar
TrainPerson
Frequent Visitor
3 years ago
Solved

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...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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"
lbendlin's avatar
lbendlin
Super User
3 years ago

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's avatar
TrainPerson
Frequent Visitor
3 years ago

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's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
      TrainPerson
      Frequent Visitor
      3 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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