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

[Arendp]

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"

[Greg_Deckler]

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. 

[Arendp]

@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?

[Greg_Deckler]

@ImkeF @edhans