Forum Discussion

alexmoller's avatar
alexmoller
Frequent Visitor
4 years ago
Solved

POST request to Power Query

Hello,

 

I am trying to make a post request that grants me an API key within power query, but I am struggling to do so. Here is some info on the request:  

 

post: https://sandbox.spearline.com/admin/api/user/token

 

headers: 

content-type: application/json

accept: application/json

 

body: 

{
"email": "[email protected]",
"password": "Kdfaweow!@fdfhiwg"
}

 

The response looks like this 

Here is how it looks from fiddler when run from postman:

Any help is greatly appreciated - many thanks in advance,

Alex.

Need Help
  • alexmoller's avatar
    alexmoller
    4 years ago

    Hi All,

     

    Finally managed to crack it - here is the request that I created which returns the API token:

     

     

7 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi alexmoller 

    Try this code which I use to make POST request to other API's.  

     

    let
    // Get the API Token
    api_url = "https://sandbox.spearline.com/",
    token_path = "admin/api/user/token",
    Username = "xxxxxxxx",
    Password = "xxxxxxxx",
    
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Username & ":" & Password), BinaryEncoding.Base64),
    
    Token_Response  = Json.Document(Web.Contents(api_url,
    [ 
      RelativePath = token_path,
      Headers = [#"Content-Type"="application/json",#"Authorization"=EncodedCredentials],
      Content=Text.ToBinary("grant_type=client_credentials")
    ]
    )
    ),
   
    // Get the token from the API response 
    token = Token_Response[token]

in
    token

     

     

    Obviously it will need some tweaking to work for you.

    To make a POST you typically need to supply some Credentials in the request and this is done by the EncodedCredentials.  However as I don't have any documentation for your API I don't know exactly how Spearline expects credentials to be supplied.

    Based on the info you have supplied, in the Token_Response it looks like the token itself will be in a field called [token].  If it's something different then you just need to change that field name.

    I've also set the Content-Type to application/json based on what you have said the API expects.

    Give it a try and let me know how you go.  

    If you have documentation on the API please share as I can then check the exact settings required in the POST request.

    regards

    Phil

     

    • alexmoller's avatar
      alexmoller
      Frequent Visitor
      4 years ago

      Thanks PhilipTreacy  - much appreciated! I did try to adjust the code you gave me but I get a "We could not authenticate with the credentials you provided" error - I will do some digging and see if I can work out whats going on.

       

      Unfortunately their documentation requires you to sign in 😞 - here is a couple of screenshots from the authentication process - hopefully that will give you a bit more of an idea

       

      Let me know if you need any more info

       

      • alexmoller's avatar
        alexmoller
        Frequent Visitor
        4 years ago

        Hi All,

         

        Finally managed to crack it - here is the request that I created which returns the API token:

         

         

    • MarkPalmberg's avatar
      MarkPalmberg
      Kudo Commander
      4 years ago

      Hi, PhilipTreacy , I wonder if you'd have a minute to help me with an API access setup I'm working on?

       

      Here's what I'm trying to connect to via Power Query:

      https://api.imodules.com/ws/21/EmailCategoryQuery.asmx

       

      If you select the GetMembersChangedDuring operation, you'll see what the various access methods look like. When I validate with my user credentials here:

      https://api.imodules.com/ws/21/EmailCategoryQuery.asmx?op=GetMembersChangedDuring

       

      with  changedFrom = 2022-03-01 and changedTo = 2022-04-01, I get data back in this format:

      In PQ, I've started this script, but I'm at the point where I'm trying to figure out how to get my results back:

      So it seems like I'm close...I just need to add a step for ingesting the results. Any thoughts you have would be very much appreciated! Thanks.

    • alexmoller's avatar
      alexmoller
      Frequent Visitor
      4 years ago

      Hi Anonymous  thanks for the link - I have just watched through, and there was not anything mentioned about passing through the credentials in the body of a post request, which is the part that I really need help with - any ideas as a starting point?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    alexmoller - Thank you for watching the video, I think the key takeaways for you to consider are:

    1. Web.Contents in Power Query create Post API calls that use Header and Boby, but you need to understand the M Language syntax to use it properly.   In you situation, you should be able to include the Headers and Json Body to receive the Token within the JSON response.   This will only work in Power BI Desktop
    2. Note your User and Password will not be secure unless you have the Custom Connector.  Power BI Desktop does not have standard OAuth 2.0 connector, so you need to develop a Custom Connector in Power Query SDK -  Installing the Power Query SDK - Power Query | Microsoft Docs