Post API Data Source body format issues

[Anonymous]

Hi all,

I have been down the rabbit whole of trying to connect to a Post API. I am able to connect to the API in Postman & R. 

Here is the curl from postman for the API:

curl --location --request POST 'https://******/token' \
--header 'Content-Type: text/json' \
--data-raw '{"GrantType":"***","Username":"***","Password":"***!","ClientId":"**","ClientSecret":""}'

Here is the R code:

payload <- '{"GrantType":"***","Username":"***","Password":"**","ClientId":"**","ClientSecret":""}'

result <- POST("https://*****/token",
body = payload,
add_headers(.headers = c("Content-Type"="application/json")))
Output <- content(result)
AccessToken <- Output$AccessToken

Here is my M code:

let
body= "{""GrantType"":""***"",""Username"":""***"",""Password"":""***"",""ClientId"":""***"",""ClientSecret':""""}",
Source = Json.Document(Web.Contents("https://******/token",
    [ 
     Headers=[#"Content-Type"="application/json"],
     Content= Text.ToBinary(body)
    ]))
in
    #"Source"

The error:

DataSource.Error: Web.Contents failed to get contents from 'https://*******/token' (400): Bad Request

I have tried:

Relative Path, like suggested here:

https://community.powerbi.com/t5/Service/Anonymous-access/td-p/19136

Square brackets at the start and end of the body, like suggested here asked by @amenne: https://community.powerbi.com/t5/Desktop/Using-a-REST-API-as-a-data-source-POST-Method-Only/td-p/244...

One of the most useful posts I found was:

https://stackoverflow.com/questions/63400612/how-do-i-retrieve-a-bearer-token-from-a-service-in-powe...

& Chris's blog https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/

Also tried, Text.ToBinary & Json.From (yet to find any other format type that can be sent).

Summary.

The body that is sent by R is character, which if memoryis correct is Text in M but PowerBI won't sent text.

Another difference I noticed is that curl data is "data-raw" where as for @amenne's post is "data".

 In short, unless there is a different way to format the body, my suspicion is that format being sent from Text.ToBinary is not the format expected by the API & that PowerBI can't sent the correct format as it can only send Binary or Json.

[Anonymous · Accepted solution]

I found the solutions, it was the format of the body;

The end point/post API was expecting a json/text file not binary. In order to send a json file and us the Json.From method I needed to format the body in to an M list. 

Here is a reference article:

https://stackoverflow.com/questions/58794715/powerbi-query-webmethod-post-returns-expression-error-w...

Here is the new code:

let
body= [GrantType="**",Username="**",Password="***",ClientId="***",ClientSecret=""],
Source = Json.Document(Web.Contents("https://****/token",
    [ 
     Headers=[#"Content-Type"="application/json"],
     Content= Json.FromValue(body)
    ]))
in
    #"Source"

Hopefully this helps others.

[Kiranbrahma]

How it is done by dynamically change the body values