Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I need to access the data from the POST API given by the client.
I have the information provided by the client:
Verb: POST
DEV Endpoint: https://clientwebsitename.azurewebsites.net/dev/masterdata/departments/get
Required header: X-Token (pass in the user’s JWT)
Returned JSON has an element named uiList that holds a list of all permitted departments.
Can anyone please help how to access the data from this API provided, I have tried most but it returns a 404 error.
Thanks,
Solved! Go to Solution.
HI @Anonymous,
>>DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.
Perhaps this issue appears when you try to use the wrong connector to recognize response data.
I'd like to suggest you navigation to the 'Source' step to confirm the result type and change to the corresponding connector in the next steps to recognize/analytics records.
Regards,
Xiaoxin Sheng
HI @Anonymous,
You can take a look at the following link about using a web connector to get data from the rest API with authorizations:
Pull data from a REST API Authentication
Sample code:
let
token = 'your token',
body= 'request body',
url = "https://clientwebsitename.azurewebsites.net/",
Source = Web.Contents(url,
[
Headers = [#"X-Token"=token],
RelativePath="dev/masterdata/departments/get",
Content = Text.ToBinary(body)
]
)
in
Source
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Thank You for the reply, I have tried the code you have provided but it gives the following error-
Expression.Error: The 'X-Token' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer.
I have tried in postman and it works, what process I have used in postman as below-
1. I have URL to get Token: (jwt)
URL: https://Clientwebsite.azurewebsites.net/dev/auth/user/authenticate
here I have provided the payload as well
2. Then another URL to get the Data
URL: https://Clientwebsite.azurewebsites.net/dev/masterdata/departments/get
here I have provided Kayname (X-Token) and jwd Token(generated from 1st step).
Please help me to retrieve the data.
Thanks
Hi @Anonymous,
It seems like you need to add the first strep into the query table to get the access token and use in the following steps, I try to modify the formula to add the first step into the formula:
let
url = "https://clientwebsitename.azurewebsites.net/",
// Uses the Twitter POST oauth2/token method to obtain a bearer token
GetJson = Web.Contents(url,
[
Headers = [#"Authorization"=authKey, #"Content-Type"="application/json"],//use the sent parameters and name as you do in postman to replace current Authorization
RelativePath="dev/auth/user/authenticate"
]
),
token = Json.Document(GetJson)['token name'],//use tokne name to repalce 'tokne name' part to extract the responsed token
Source = Web.Contents(url,
[
Headers = [#"X-Token"=token, #"Content-Type"="application/json"],
RelativePath="dev/masterdata/departments/get"
]
),
Result=Json.Document(Source)
in
Result
BTW, how did you post the authorizations in your requests? In the header, body or concatenate into query string? My formula is add authorized into the headers and it may not suitable for all scenarios.
Chris Webb's BI Blog
Regards,
XIaoxin Sheng
Hi @v-shex-msft,
Thank you for the suggestion, But it gives the error,
What I have tried:
let
url = "https://clientwebsitename.azurewebsites.net/",
body = "{
""grant_type"": ""client_credentials"",
""TenantId"": ""Client2"",
""UserSignon"": ""corp2019"",
""Password"": ""test""
}",
GetJson = Web.Contents(url,
[
Headers = [#"Content-Type"="application/raw"],
Content=Text.ToBinary(body),//use the sent parameters and name as you do in postman to replace current Authorization
RelativePath="dev/auth/user/authenticate"
]
),
token = Json.Document(GetJson)['jwt'],
Source = Web.Contents(url,
[
Headers = [#"X-Token"=token, #"Content-Type"="application/json"],
RelativePath="dev/masterdata/departments/get"
]
),
Result=Json.Document(Source)
in
Result
The Query is run successfully when suppressed the ['jwt'] from 2nd query and It generated the 2 Token with Value as below
The problem is ['jwt] is not worked, it gives error as" Invalid Identifier".
Thanks
Hi @Anonymous,
My fault, it seems like I added additional characters '' in the sample formula. In fact, the '' characters are not needed. Please use [jwt] to replace ['jwt'] and try again.
BTW, please do mask on the sensitive data from your sample formal and snapshots.
Regards,
Xiaoxin Sheng
Thank you @v-shex-msft,
But it is still showing the following error:
DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.
Thanks
HI @Anonymous,
>>DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.
Perhaps this issue appears when you try to use the wrong connector to recognize response data.
I'd like to suggest you navigation to the 'Source' step to confirm the result type and change to the corresponding connector in the next steps to recognize/analytics records.
Regards,
Xiaoxin Sheng
Your request is incomplete. POST requires a payload in the request body.
Hi @lbendlin,
Thanks for the reply.
Yes I have another link as well as Payload contains the username and password to access the data from the given link
Thanks
Here is an example. Use Json.FromValue to binary encode your payload
let
URL = "http://xxx/tstat",
headers = [#"Content-Type"="application/json"],
data = Json.FromValue([tmode = 2,t_cool = Setpoint,hold = 0]),
web = Web.Contents(URL, [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
result = Json.Document(web)
in
result
This is Power Query (also referred to as M) . You use that to connect to your data sources.
If you are new to Power BI then you may want to start with an easier project. Running POST calls is rather advanced.
Hi @lbendlin
Sorry It's my bad, I mean to say, is it need to type in the Advance editor of Power Query Editor?
Also having one question, Where to add the Header Name (i.e,- X-Token) and jwd token generated from the payload.
Yes, this goes in the advanced editor. Same for the header (as shown in the example)
When you get the data back from the POST call you need to parse it as needed. Again this is done in the same Advanced editor.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |