cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amenne Frequent Visitor
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

Accepted Solutions
Moderator Eric_Zhang
Moderator

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


@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

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

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

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

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

9 REPLIES 9
amenne Frequent Visitor
Frequent Visitor

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

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

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

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!

 

 

Moderator Eric_Zhang
Moderator

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


@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

amenne Frequent Visitor
Frequent Visitor

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

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

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


@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

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

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

ephysthos Visitor
Visitor

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

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

 

Thanks,
G.

Anonymous
Not applicable

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

Anonymous
Not applicable

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

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors