Using a REST API as a data source - POST Method Only

[amenne]

1.) I am trying to connect PowerBI to a datasource with REST API that supports XML or JSON.

Below is the Advanced editor View I am trying to execute.

Here is the API Document: https://secure3.saashr.com/ta/docs/rest/index.html#_login

I think I am missing the "Content" section or excuting that improperly.

2.) Also, after I get authenticated, I would like to retrieve:
https://secure3.saashr.com/ta/docs/rest/index.html#post__employee_contacts

Can someone comment on what I may be doing incorrectly?
Also, offer some Advanced script code to return the second request?

Thank you!!!

let
 authKey = {
  "credentials", {
    "username", "REST.USER",
    "password", 1ABC-123!",    (Sample info for security)
    "company", "1234567"          (Sample info for security)
  }
},
 url = "https://secure3.saashr.com/ta/rest/v1/login",
 // Uses the authentication/token method to obtain a token
 GetJson = Web.Contents(url,
     [
         Headers = [#"Content-Type"="application/json",#"Api-Key"="65893wsw37l1rjctnuk1568xfpusjdv8"],
         Content = (token),
         RelativePath="/Authentication/AuthorizeUser"
     ]
 )
in
    GetJson

[Eric_Zhang · Accepted solution]

---

@amenne wrote:

 I got the API working in Postman, attached above is the cURL.

Any help to get tihs converted to Power Query M?

Thanks!

---

@amenne

I don't have the account of your site for testing purpose, however I think it is the same way calling a POST Power BI API. Try to add the data JSON as Content = Text.ToBinary(dataJson)]).

let
     
    url = "https://api.powerbi.com/beta/72f988bf-86f1-41af-91ab-2d7cd011db47/datasets/29f1e104-5e56-4247-8712-8f109102109f/rows?key=cZs8uA30GFpBHTi8bCSEbt2RK6fZn3QuZDnp6pgsyk1JofKe49WjSXxbBiMlqb1NXjkCb5sSHeNS52GFIxbCnA%3D%3D",
    body = "
[
{
""VALUE"" :198.6
}
]
",
    Source = Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body)])
in
    Source

[Anonymous · Accepted solution]

how could I use POST method using username and password? (my API does not accept tokens)
I am getting error 405 METHOD NOT ALLOWED on my code below.

It seems username and password are not pushed inside API.
I am new to M language and not used to the correct syntax I think...

let
     apiUrl = "http://xxxxx",
     options = [Headers =[#"Content-Type"="application/json",
                        #"Authorization" = "(base64-encoded username: password)"]],
     Value = Web.Contents(apiUrl,options)

in
     Value

[akashgera]

Hi Folks,
I am also using REST API to fetch the data from 3rd party using POST method, I have used the same parameters in Header, Body in postman, and it is giving me the data in JSON format, but when I am using the same thing in power query, it is giving me an error: Response Msg: Invalid APi credentials, Response code:11 & response : NULL
Please have a look in my power query below and help me what is wrong in this :-

let
ApiOwner= "XXXXXXXX",
ApiKey="XXXXXXXXXXXXXXXXXXXXXXXXX",
url = "https://dentalkart.vineretail.com/RestWS/api/eretail/v3/sku/inventoryStatus",
header= [#"Authorization" = ApiKey & ApiOwner,
#"Content-Type" = "application/x-www-form-urlencoded"],

RequestBody=
"{
""skuCodes"":""[]"",
""fromDate"":""18/06/2021 15:40:30"",
""toDate"":""23/06/2021 15:40:30"",
""pageNumber"":""1"",
""dropShipFlag"":""no"",
""locCode"":""
}",

webdata = Web.Contents(url, [Content = Text.ToBinary(RequestBody), Headers=header]),
response = Json.Document(webdata)

in
response

Any kind of help would really be appreciated !

[amenne]

Here is some more info... Here is a VB Script that I use to accomplish the same:

'*****************************
'* Set Variables
'*****************************
Company = "xxxxxxx"
APIUserName = "REST.USER"
APIPassword = "xxxxxxxx"
APIKey = "REMOVED"
ReportID = "19538888"
baseurl = "https://secure3.saashr.com:443/ta/rest/v1/"
'*****************************
'* Process
'*****************************
Token = getToken(Company,APIUserName,APIPassword,APIKey,BaseURL)
MsgBox Token
Request = "report/saved/" & ReportID
Data = executeRequest(Token, "GET", APIKey, Request, "text/xml",BaseURL)
MsgBox data

'*****************************
'* Functions
'*****************************
Function getToken(sCompany,sAPIUserName,sAPIPassword,sAPIKey,sBaseURL)    '^This Function section gets the Token to use in future calls
    Set objHTTP = CreateObject("Microsoft.XMLHTTP")
    body = ""
    body = body & "<?xml version='1.0' encoding='UTF-8'?>" & vbCrLf
    body = body &  "<login_request>" & vbCrLf
    body = body &  "    <credentials>" & vbCrLf
    body = body &  "        <username>" & sAPIUserName & "</username>" & vbCrLf
    body = body &  "        <password>" & sAPIPassword & "</password>" & vbCrLf
    body = body &  "        <company>" & sCompany & "</company>" & vbCrLf
    body = body &  "    </credentials>" & vbCrLf
    body = body &  "</login_request>" & vbCrLf
    Set objHTTP = CreateObject("Microsoft.XMLHTTP")
    objHTTP.open "POST", sBaseURL & "login", false
    objHttp.SetRequestHeader "Api-Key", sAPIKey
    objHttp.SetRequestHeader "Content-Type", "text/xml"
    objHTTP.send body
    response = objHTTP.responseText
    a = Split(response,""",""")
    getToken = Mid(a(0),11,1000)
End Function

Function executeRequest(sToken, sAction, sAPIKey, iRequest, sContentType, sBaseURL)  '^This Function return the results of a Saved Report
    Set objHTTP = CreateObject("Microsoft.XMLHTTP")
    objHTTP.open sAction, sbaseurl & iRequest, false
    objHttp.SetRequestHeader "Content-Type", sContentType
    objHTTP.SetRequestHeader "Authentication", "Bearer " &  sToken
    objHTTP.send
    executeRequest = objHTTP.responseText
End Function

[amenne]

 I got the API working in Postman, attached above is the cURL.

Any help to get tihs converted to Power Query M?

Thanks!

[Eric_Zhang · Accepted solution]

---

@amenne wrote:

 I got the API working in Postman, attached above is the cURL.

Any help to get tihs converted to Power Query M?

Thanks!

---

@amenne

I don't have the account of your site for testing purpose, however I think it is the same way calling a POST Power BI API. Try to add the data JSON as Content = Text.ToBinary(dataJson)]).

let
     
    url = "https://api.powerbi.com/beta/72f988bf-86f1-41af-91ab-2d7cd011db47/datasets/29f1e104-5e56-4247-8712-8f109102109f/rows?key=cZs8uA30GFpBHTi8bCSEbt2RK6fZn3QuZDnp6pgsyk1JofKe49WjSXxbBiMlqb1NXjkCb5sSHeNS52GFIxbCnA%3D%3D",
    body = "
[
{
""VALUE"" :198.6
}
]
",
    Source = Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body)])
in
    Source

[amenne]

So what is the syntax to have the multiple body elements?

[Eric_Zhang]

---

@amenne wrote:
So what is the syntax to have the multiple body elements?

---

@amenne

What do you mean multiple body elements? As per my knowledge, a POST http request only has one body, in JSON/XML/form-data etc.

[Anonymous · Accepted solution]

how could I use POST method using username and password? (my API does not accept tokens)
I am getting error 405 METHOD NOT ALLOWED on my code below.

It seems username and password are not pushed inside API.
I am new to M language and not used to the correct syntax I think...

let
     apiUrl = "http://xxxxx",
     options = [Headers =[#"Content-Type"="application/json",
                        #"Authorization" = "(base64-encoded username: password)"]],
     Value = Web.Contents(apiUrl,options)

in
     Value

[ephysthos]

I have the same probleme. Have you found a workaround ?

Thanks,
G.

[Anonymous · Accepted solution]

REST API method POST

I manage to make it work in my API using a little bit different code. POST Rest API.
My API needs full URL, and I needed to add timeout parameter. For default, Power BI has a 5 minute timout limit.
The way the timeout duration works is (day,hour,minute,second). So my code below has a 2 hours timeout limit.

for authentication, my API uses login password encoded on base 64.

The command #"Authorization" = "base64-encoded user: password" did not work, so I changed to #"Authorization" = "basic dXNlcjpwYXNzd29yZA==". (there is no space after "user:", but ":" and "p" makes a useless emoji... userassword)

Where "user: password" equals to "dXNlcjpwYXNzd29yZA==", using https://www.base64encode.org/ to encode/decode.

let
url = "http://full.api/url/here/including/all/subfolders",
body = "{""parameter as date"":""2017-10-31"",
""parameter as boolean"":true,
""parameter as number"":3
}",

Source = Json.Document(Web.Contents(url,[
Headers =[#"Content-Type"="application/json", #"Authorization" = "basic "],
Content = Text.ToBinary(body) , Timeout=#duration(0,2,0,0)
]
)),

in
#"Source"

[Anonymous]

yes.

check my solution for my case at the other post.

https://community.powerbi.com/t5/Desktop/Connect-to-a-Web-Service-sending-parameters/m-p/295990#M130...