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"