Forum Discussion

Mitchblue006's avatar
Mitchblue006
Advocate I
3 years ago
Solved

List Generate in advanced editor

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] ...
General Comment
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    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

PhilipTreacy's avatar
PhilipTreacy
Super User
3 years ago

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's avatar
Mitchblue006
Advocate I
3 years ago

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.