Error 429 too many request on Power Query

[felipe_pinto]

Hello Guys,

I'm haveing troubles to end the follwing code.

I need to find a solution to error 429...the API has a limit of 15 request per minute...could someone help me?

Code:

= (CNPJ as number) =>

let
Fonte = Json.Document(Web.Contents("URL" &Text.PadStart(Number.ToText(CNPJ),14,"0")& "&plugin=RF"))
in
Fonte

Best regards

[edhans]

I think that is the site you are hitting, not Power Query itself.

[felipe_pinto]

Hello Edhans,

Thank you for your reply!!

Actually the site is working. The problem is the code beacuse I need to adpat it to wait 60 seconds before to consult another 15 codes. As I said, the API has a limit of 15 request a minute.

You will find below a code that could correct and solve my problem, but I'm cannot add it to my code:

Here you'll find the link with the codes below: https://4pbi.com/handling-http-errors-with-m-language/

Code:

Result = Value.WaitFor(

        (iteration) =>

            let

                content = Web.Contents(Url, [ManualStatusHandling = {429}]),

                buffered = Binary.Buffer(content),

                status = Value.Metadata(content)[Response.Status],

                actualResult = if status = 429 then null

                    else Json.Document(Text.FromBinary(buffered))

           in

                actualResult,

        (iteration) => #duration(0, 0, 0, 5),

        10

         )

******************************************************************************

Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>

    let

        list = List.Generate(

            () => {0, null},

            (state) => state{0} <> null and (count = null or state{0} < count),

            (state) => if state{1} <> null then {null, state{1}} else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},

            (state) => state{1})

    in

        List.Last(list);

    in

        Result;

****************************************************************

Could you help me adding this code to my one?

[v-yingjl]

Hi @felipe_pinto ,

As the article mentioned, need to add Value.Waitfor() definition in the query, after combining with your code, the final query could be like this:

(CNPJ as number) =>
    let
        Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>
        let
            list = List.Generate(
                () => {0, null},
                (state) => state{0} <> null and (count = null or state{0} < count),
                (state) => if state{1} <> null then {null, state{1}} else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},
                (state) => state{1})
        in
            List.Last(list),

        Result = Value.WaitFor(
 
            (iteration) =>
 
            let
 
                content = Web.Contents("URL" &Text.PadStart(Number.ToText(CNPJ),14,"0")& "&plugin=RF", [ManualStatusHandling = {429}]),
 
                buffered = Binary.Buffer(content),
 
                status = Value.Metadata(content)[Response.Status],
 
                actualResult = if status = 429 then null
 
                    else Json.Document(Text.FromBinary(buffered))
 
           in
 
                actualResult,
 
            (iteration) => #duration(0, 0, 1, 0),
 
            10
 
         )
 
    in
 
        Result

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[felipe_pinto]

Hello @v-yingjl , how are you doing?

Thank you so much for your help and your time.

I got the combination you did, I'll try that and see if it's gonna work 🙂

Thank you so much one more time!!