Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amenne
Frequent Visitor

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

3 ACCEPTED SOLUTIONS


@amenne wrote:

Screen Shot 2017-09-02 at 10.30.16 PM.png

 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

 

View solution in original post

Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

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... userSmiley Tongueassword)

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"

View solution in original post

10 REPLIES 10
akashgera
Helper I
Helper I

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

akashgera_0-1626965016686.png

Any kind of help would really be appreciated !

amenne
Frequent Visitor

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
Frequent Visitor

Screen Shot 2017-09-02 at 10.30.16 PM.png

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

Any help to get tihs converted to Power Query M?

Thanks!

 

 


@amenne wrote:

Screen Shot 2017-09-02 at 10.30.16 PM.png

 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

 

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


@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
Not applicable

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

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

 

Thanks,
G.

Anonymous
Not applicable

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... userSmiley Tongueassword)

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
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.