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

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