List Generate in advanced editor

[Mitchblue006]

Hi everyone

I am receiving errors in syntax when I try loop my scroll ID through my base url, I guess I should paste the screenshot, however the error seems to be at the end...

    each [data]
)

what other syntax will allow this query to run?

[v-henryk-mstf]

Hi @Mitchblue006 ,

Whether the advice given by @PhilipTreacy  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Mitchblue006]

Unfortunately, the solution had not solved the problem, the authorization in M language was one issue, and don't know how else to authenticate API keys other than the PayPal example, still trying to solve problem of recursive API calls in M query

[PhilipTreacy]

Hi @Mitchblue006 

Please explain what the issue you are now facing is.  You mention recursion but I'm guessing you are trying to call an API repeatedly to get several pages of data?

If you can provide as much detail as possible that'll help.

Regards

Phil

[Mitchblue006]

Hi Philip, you have helped me realize how to write M Query with the authentication keys, but my problem is actually the recursive API call problem...

I tried the following which has been featured on the community...

let
    Source = List.Generate(
    () => [ data = Json.Document(Web.Contents("https://api.paystack.co/subscription/?perPage=100&page=1",[Authorization = [bearer sk_live_***]]), 
            ScrollID = "subscription_code"
          ],

    each [ScrollID] < "null" ,  // What is the condition to terminate the loop ?

    each [ ScrollID = [data][ScrollID], data = Json.Document(Web.Contents("https://api.paystack.co/subscription/?perPage=100&page=1&scrollid="&[ScrollID], [Authorization = [sk_live_***]])) ],

    each [data]
)
in
    Source

The issue is that I am able to call this query in simple web connection, but 'cancelled date' is missing, so the advice from Paystack admin was to call each [subscription/sub_code] recursively, because the power query would not know what the parameter is without the first call, therefore the above code tries to insert the text that will call the subsequent API call to generate 'cancelled date'...I am sending you screenshots showing you the API call results when called individually...

Then, as you can see, there is no "cancelledAt" which is the result I need!

Now it only shows on subsequent call, on item 15...

Hope that makes sense! 

I need subsequent API call to list all these results, and I am struggling with M query.

[PhilipTreacy · Accepted solution]

Hi @Mitchblue006 

Authorization should be sent as part of a Headers record e.g.

data= Json.Document(Web.Contents(api_url,
   [ 
     Headers = [#"Authorization"=EncodedCredentials]
   ]
   )
   )

Typically the Auth credentials would be encoded before sending too, something like this

    ClientID = "xxxxxxxx",
    Secret = "xxxxxxxx",
    
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),

Apart from this I don't get any other syntax errors in the code.

This article has info and examples of connecting to an API that requires authentication

Connecting to an OAuth API Like PayPal With Power Query • My Online Training Hub 

regards

Phil

[Mitchblue006]

l

let
    Source = List.Generate(
    () => [ data = Json.Document(Web.Contents("https://api.paystack.co/subscription/?perPage=100&page=1",[Authorization = [sk_live_***]]), 
            ScrollID = "subscription_code"
          ],

    each [ScrollID] < "null" ,  // What is the condition to terminate the loop ?

    each [ ScrollID = [data][ScrollID], data = Json.Document(Web.Contents("https://api.paystack.co/subscription/?perPage=100&page=1&scrollid="&[ScrollID], [Authorization = [sk_live_***]])) ],

    each [data]
)
in
    Source

[PhilipTreacy]

Hi @Mitchblue006 

Sorry, can't debufg this without the code, please supply the file with your code.

regards

Phil