Help with POST API

[smeredith]

Hi I have been pouring over the internet trying to solve this problem, but am stumped. If there is helpful information elsewhere I may have missed, please point me in the right direction.

We have a piece of equipment which stores data to a cloud service. The service provides JSON files with the data via REST API. Currently we are using Postman to generate the POST command using Basic Auth to recieve the token. We then use the token to run several other GET requests to get the JSON files we need. The JSON files are then edited using notepad++, and the imported into PowerBI via queries. I am confident all of this can be done within Power BI, I just don't know how. Here is the console data from Postman to receive the token:

POST

https://forneyvaultapi-v5staging.azurewebsites.net/api/Authenticate

13:31:58.962

Pretty

Raw

POST /api/Authenticate HTTP/1.1

Content-Type: application/json

Authorization: Basic ZWRlbmlubm92YXBpQGZvcm5leXZhdWx0LmNvbTpjcUBebV8rQmhnNl5laEdQ

User-Agent: PostmanRuntime/7.16.3

Accept: */*

Cache-Control: no-cache

Postman-Token: af82fe58-e7fe-4a76-a4f3-64fd65ae141f

Host: forneyvaultapi-v5staging.azurewebsites.net

Accept-Encoding: gzip, deflate

Content-Length: 84

Cookie: ARRAffinity=84bf4a47daca0e8b2456e7f9b916668a7467930da7d6cbd0753e915d0fcce476

Connection: keep-alive

{

"username": "name@somewhere.com",

"password": "password"

}

HTTP/1.1 200 OK

Cache-Control: no-cache

Pragma: no-cache

Content-Length: 1490

Content-Type: application/json; charset=utf-8

Content-Encoding: gzip

Expires: -1

Vary: Accept-Encoding

Server: Microsoft-IIS/10.0

X-AspNet-Version: 4.0.30319

X-Powered-By: ASP.NET

Date: Thu, 05 Sep 2019 19:31:59 GMT

"long token string"

[v-piga-msft]

Hi @smeredith ,

If I understand your sceanrio correctly that you want to get data with the REST API in power bi desktop?

If so, please refer to the similar threads firstly.

https://community.powerbi.com/t5/Desktop/Using-a-REST-API-as-a-data-source-POST-Method-Only/td-p/244...

https://community.powerbi.com/t5/Desktop/Pull-data-from-API-using-POST-method-in-Power-BI/td-p/57472...

https://community.powerbi.com/t5/Desktop/How-to-run-POST-request-in-M/td-p/457138

If you need any other help, free to ask.

Best  Regards,

Cherry

[smeredith]

Thank you for the information. I have made several attempts, but generally fail with the 405 Method not allowed error. Presently I am only trying to receive the bearer token from the URL using basic auth.

Attempt 1:

let

Source = Json.Document(Web.Contents("https://forneyvaultapi-v5staging.azurewebsites.net/api/Authenticate"))
in
Source
Attempt 2:
let
auth_key ="Basic ZWRlbmlubm92YXBpQGZvcm5leXZhdWx0LmNvbTpjcUBebV8rQmhnNl5laEdQ",
url = "https://forneyvaultapi-v5staging.azurewebsites.net/api/Authenticate",
header =[#"Authorization"=auth_key,
#"Content-Type"="application/json; charset=utf-8"],
content1="{
username=xxx@xx.com,
password=password
}",
webdata1=Web.Contents(url, [Headers=header,Content=Text.ToBinary(content1)]),
response1=Json.Document(webdata1)
in
response1
Attempt 3:
let
authkey="Basic ZWRlbmlubm92YXBpQGZvcm5leXZhdWx0LmNvbTpjcUBebV8rQmhnNl5laEdQ",
url = "https://forneyvaultapi-v5staging.azurewebsites.net/api/Authenticate",
source = Json.Document(Web.Contents(url,[Headers = [Authorization = authkey, #"content-type" = "application/json:charset = utf-8"]]))
in
source

Attempt 4:

let
url="https://forneyvaultapi-v5staging.azurewebsites.net/api/Authenticate",
auth_key = "BASIC ZWRlbmlubm92YXBpQGZvcm5leXZhdWx0LmNvbTpjcUBebV8rQmhnNl5laEdQ",
header= [
#"Content-Type" = "application/json; charset=utf-8",
#"Authorization" = auth_key,
#"Accept"="*/*",
#"Cache-Control"="no-cache",
#"Host"="forneyvaultapi-v5staging.azurewebsites.net",
#"Accept-Encoding"="gzip, deflate",
#"Content-Length"=84,
#"Connection"="keep-alive"
],

webdata = Web.Contents(url),
response = Json.Document(webdata)
in
response

Attempt 5:

let
url="https://forneyvaultapi-v5staging.azurewebsites.net/api/Authenticate",
header= [
#"Content-Type" = "application/json",
#"Authorization" = auth_key,
#"Accept"="*/*",
#"Cache-Control"="no-cache",
#"Host"="forneyvaultapi-v5staging.azurewebsites.net",
#"Accept-Encoding"="gzip, deflate",
#"Content-Length"=84,
#"Cookie"="ARRAffinity=84bf4a47daca0e8b2456e7f9b916668a7467930da7d6cbd0753e915d0fcce476",
#"Connection"="keep-alive",
#"username"="xxxx@xxx.com",
#"password"="password"
],
auth_key = "BASIC ZWRlbmlubm92YXBpQGZvcm5leXZhdWx0LmNvbTpjcUBebV8rQmhnNl5laEdQ",

body="{""username"":""xxxx@xxx.com"",""password"":""password""}",

webdata = Web.Contents(url),
response = Json.Document(webdata)
in
response

Attempt 6:

let
url="https://forneyvaultapi-v5staging.azurewebsites.net/api/Authenticate",
body="{""username"":""xxxxx@xxx.com"",""password"":""password""}",
Source = Json.Document(Web.Contents(url,[Headers= [
#"Content-Type" = "application/json",
#"Authorization" = auth_key,
#"Accept"="*/*",
#"Cache-Control"="no-cache",
#"Accept-Encoding"="gzip, deflate",
#"Cookie"="ARRAffinity=84bf4a47daca0e8b2456e7f9b916668a7467930da7d6cbd0753e915d0fcce476",
#"Connection"="keep-alive",
#"username"="xxxx@xxx.com",
#"password"="password"
]])),

auth_key = "BASIC ZWRlbmlubm92YXBpQGZvcm5leXZhdWx0LmNvbTpjcUBebV8rQmhnNl5laEdQ"

in
#"Source"