Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TrainPerson
Frequent Visitor

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

TrainPerson_0-1683811236435.png

 

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.

1 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"

View solution in original post

7 REPLIES 7
TrainPerson
Frequent Visitor

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.

 

TrainPerson_0-1684134160387.png

 

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.

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?

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"

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. 

 

TrainPerson_0-1684161468373.png

 

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.