Forum Discussion

Mitchblue006's avatar
Mitchblue006
Icon for Advocate I rankAdvocate 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]
)

what other syntax will allow this query to run?

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

7 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    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

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    3 years ago

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

      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's avatar
        PhilipTreacy
        Icon for Super User rankSuper 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
    Icon for Advocate I rankAdvocate I
    3 years ago

    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