Query to Scrap URLs with a pause every xx URLs

Hi,

Many thanks for your help Angelia.

It took me some time to manage to do what I want, but finally I did it thanks to the link shared by Angelia and a few things from here and there.

I am going to summarize the various steps in case it can help some of you ;-)

01 – First query: getting data list to scrap, and setting wait parameters:

===============================================================

I have a first query using an api which collect a list of data used to scrap an URL of the same website.

==> So this query collect a list of data

==> then I add an index column to get the row count from 1 to xxx

==> then I use a modulo formula to set a pause in the query every xx rows.

Here the sample of this first query:

let
    Source = Json.Document(Web.Contents("https://api.xxxxxxx")),
…….
    #"Index ajouté" = Table.AddIndexColumn(#"Column1 développé", "Index", 1, 1),

    Row_Mod_Multiple = 3,

    #"Personnalisée ajoutée" = Table.AddColumn(#"Index ajouté", "Wait", each if Number.Mod([Index], Row_Mod_Multiple) = 0 then "yes" else "no")
in
    #"Personnalisée ajoutée"

Thanks to the modulo formula with a row multiple of 3, every 3 rows the query will wait xxx seconds (or minutes) before to proceed with the next row.

02 – Second query: wait formula:

===============================================================

This second query is the wait function (this query is used to add a wait time of xxx seconds), make sure to name it “Wait_Seconds”:

let
    Wait = (seconds as number, action as function) =>
            if (List.Count(List.Generate(() => DateTimeZone.LocalNow() + #duration(0,0,0,seconds), (x) => DateTimeZone.LocalNow() < x, (x) => x)) = 0)
            then null else action()
in
    Wait

This wait function will be used in the third query.

03 – Third query: scrap urls and wait every xxx rows:

===============================================================

let
    Source = #"Query1",

    #"Personnalisée ajoutée" = Table.AddColumn(Source, "id", each if [Wait] = "yes" then Wait_Seconds(5, () => fnGetdata([URL values]) ) else fnGetdata ([URL values]) ),
……

This last query will scrap the list of URLs and will wait 5 seconds every 3 rows.

As some website are blocking IP address which are making too many requests per minutes, this approach can help to avoid the issues.

Hope it can help ;-)

Best,

G