POST request to Power Query

[alexmoller]

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": "myname@myemaildomain.com",
"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.

[PhilipTreacy]

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

[MarkPalmberg]

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]

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 · Accepted solution]

Hi All,

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

[Daryl-Lynch-Bzy]

@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 

[Daryl-Lynch-Bzy]

Hi @alexmoller , please consider watching this video to understand and trouble-shoot.

Accessing API and web service data using Power Query - by Chris Webb - YouTube

[alexmoller]

Hi @Daryl-Lynch-Bzy  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?