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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Arendp
Helper III
Helper III

API query with loop takes too long, any suggestions to improve?

The following web.content loop takes too long.

There are around 490000 lines, which should be able to handle, but it takes hours. And in the online environment it times out due to 2 hours refresh limit.

The offset is max 1000 lines per API call. 

I am not sure about the max amount of lines, so I take 500000 as a limit. Would it help to determine the # of lines?

Any suggestions on how to improve this API call?

 

let
  Source = List.Generate(
            ()=>[Counter=0],
            each [Counter]<500000,
            each [Counter=[Counter]+1000],
            each Json.Document(Web.Contents("https://api-url.nl/test/rest/api/v1/01/products", 
                        [   
                                Query=[offset=Text.From([Counter]),limit=Text.From(1000)] 
                        ]
                                ))
        ),
  #"Geconverteerd naar tabel" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Column1 uitgevouwen" = Table.ExpandListColumn(#"Geconverteerd naar tabel", "Column1"),
  #"Column1 uitgevouwen1" = Table.ExpandRecordColumn(#"Column1 uitgevouwen", "Column1", {"description", "productCode", "searchName", "unitCode", "blocked", "inactive", "compositionType", "isAssembly"}, {"description", "productCode", "searchName", "unitCode", "blocked", "inactive", "compositionType", "isAssembly"}),
  #"Rijen gefilterd" = Table.SelectRows( #"Column1 uitgevouwen1", each ([inactive] = false)),
  #"Kolommen transformeren" = Table.TransformColumnTypes(#"Rijen gefilterd", {{"description", type text}, {"productCode", type text}, {"searchName", type text}, {"unitCode", type text}, {"blocked", type text}, {"inactive", type text}, {"compositionType", type text}, {"isAssembly", type text}}),
  #"Fouten vervangen" = Table.ReplaceErrorValues(#"Kolommen transformeren", {{"description", null}, {"productCode", null}, {"searchName", null}, {"unitCode", null}, {"blocked", null}, {"inactive", null}, {"compositionType", null}, {"isAssembly", null}})
in
  #"Fouten vervangen"

 

 

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

I would suggest using Query Diagnostics on the Tools tab of Query Editor to help pin point the line(s) that is/are taking so long. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  Hello Greg, Thanks for your answer.

I have attached an export of the diagnostics.

This is only from the loading of the example in Desktop. With only 1 query, but you get what I mean.

Link to spreadsheet 

Something with the Path and Group Id? 

For example line 6 and 7 are the same, but 6 is running slower than 7. The only diff between 6 and 7 is Path and Group Id.

You have an idea what it says?

@ImkeF @edhans


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors