Forum Discussion
Using a REST API as a data source - POST Method Only
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
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!
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 Sourcehow 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
ValueREST 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... user
assword)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"
10 Replies
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 FunctionI got the API working in Postman, attached above is the cURL.
Any help to get tihs converted to Power Query M?
Thanks!
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!
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
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
responseAny kind of help would really be appreciated !
