Forum Discussion
Connect to a Web Service sending parameters
Hi Community
I am trying to access a wb API. I am connecting using web connector. I need data based upon some query valuess.
I am able to access the website for the basic table, but not sure how to fetch specific parameters or get data based upon those parameters.
Here is what I am trying:
let
authkey = "Basic my_authorization_key",
url = "https://api.mywebsite.com/social/page/post",
source = Json.Document(Web.Content(url,[Headers = [Authorization = authkey, #"content-type" = "application/json:charset = utf-8"]))
in
source
The problem starts that I need to provide information ie
{ "profile": "12345678", "date_start": "2017-06-01", "date_end": "2017-07-30", "fields": ["id"], "limit": 5 }
Can someone please help where to include this line or how to accomodate this query into the main query above to fetch the values.
Any help in this regard would be highly appriciated.
regards
got my query working.
Thanks everyone.
34 Replies
I found it not so easy to get data via POST so I am pasting here what I did in case this helps someone else in the future.
I created the following blank query:
= let body = "The POST method body here", Data= Web.Contents("https://yourusrlhere",[Content=Text.ToBinary(body),Headers=[#"Content- Type"="application/json"]]), DataRecord = Json.Document(Data), Source=DataRecord in Source- ImkeF
Community Champion
I think you need to check the API-documentation for that.
Check out this post with a sample for a Dropbox-connector: https://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-data-source-hosted-on-Dropbox/td-p/67946
Hi ImkeF.
I'm afraid I have yet Another API question.
I am new to APIās and have been trying to do a POST request to get some timesheet data. However, whatever code I try to use fails (it seems like the body/query is the main issue). My latest attempt is below and itās giving me an error on āStartDateā. Are you able to help before I consider changing careers!
let
url = "myurl ",
auth_key = GetAccessToken,
header= [#"Authorization" = auth_key,
#"Content-Type" = "application/json; charset=utf-8"],
query = [
""StartDate"":""2022-10-31T00:00:00"",
""EndDate"":""2022-11-10T00:00:00"",
""fields"":""[Date, Status, CompanyName, CompanyReference, ApproverName, EntryQuantity]"",
""TimesheetStatusFilters"":""[Approved, Exported]""
],
webdata = Web.Contents(url, [Headers=header,Query = query]),
response = Json.Document(webdata)
in
response
Many thanks
Thanks Anonymous ImkeF.
I have checked with API documentation. The information I had provided was given to me by api doc.
This is how I am trying but getting error
let
auth_key ="Basic TWpjeU16TTNYelEyT=",
base_url = "https://api.mywebsite.com/",
extension = "allpages/page/posts",
url = base_url &extension,
header= [#"Authorization" = auth_key,
#"Content-Type" = "application/json; charset=utf-8"],
query = "{
""profile"":""123456789"",
""date_start"":""2017-07-01"",
""date_end"":""2017-08-01"",
""fields"":""[id]"",
""limit""=5}",
webdata = Web.Contents(url, [Headers=header,Query = query]),
response = Json.Document(webdata)in
responseCan someone please help where I am going wrong :(
- ImkeF
What does the error message say? ;-)
I've been trying to do this same thing a variety of ways and always run into an error (usually a 400 error), but now with this way, I am getting a "Please specify how to connect" even though I have hard coded the authorization header.. any thoughts?
let auth_key = "Bearer MyApiKey", url = "https://myapi.com/analytics", header = [#"X-Impersonate-User"="UserKeyHere", Authorization=auth_key, #"Content-Type"="application/json"], content = "{ ""start_date"":""2022-08-01T08:00:00.000Z"", ""end_date"":""2022-08-10T08:00:00.000Z"", ""space_ids"":[""1234""], ""time_resolution"":""day"", }", webdata = Web.Contents(url, [Headers=header,Content = Text.ToBinary(content)]), response = Json.Document(webdata) in response- ImkeF
Make sure to click "Anonymus"-authentication on the permissions of the data source settings. Everything else won't work for POST requests.
Authorization that will be sent via the headers should be picked up regardless. - ImkeF
Hi MAS42 ,
if you want to send a POST request via Power Query, you have to use the Content parameter. Could it be that what you need to pass into it now sits in your Query parameter?
If so, please try the following. I have removed the double-quotes to make it a bit more readable:let url = "myurl ", auth_key = GetAccessToken, header= [#"Authorization" = auth_key, #"Content-Type" = "application/json; charset=utf-8"], content = Json.FromValue( [ StartDate="2022-10-31T00:00:00", EndDate="2022-11-10T00:00:00", fields="[Date, Status, CompanyName, CompanyReference, ApproverName, EntryQuantity]", TimesheetStatusFilters="[Approved, Exported]" ]), webdata = Web.Contents(url, [Headers=header,Content = content]), response = Json.Document(webdata) in responseOtherwise please check your API documentation to see what needs to go into the body and what needs to stay in query parameters.
I feel your pain, been there as well šThings will clear up after a couple of trial and errors...
Have you read this article about POST requests?: Chris Webb's BI Blog: Web Services And POST Requests In Power Query Chris Webb's BI Blog (crossjoin.co.uk)Hi Imke, many thanks for your very quick reply. Unfortunately, the webdata line is still throwing up a āWe cannot convert a value of type Function to type Textā error. Incidentally, if I use single/double quotes, PQ throws up a syntax error.
As far as I can tell the documentation simply says that the query āGenerates a ā¦ report based on the parameters in the report settings model in the bodyā
For debugging purposes I reduced the number of fields, etc, to be returned to the bare minimum while still returning some data.
If it helps find the error, Iāve been using Postman (Iām a novice with this as well so am blundering around this at the same time!) and was able to extract some data. This is the body structure:
{
"StartDate": "2022-10-31T00:00:00",
"EndDate": "2022-10-31T00:00:00",
"ReportFields": [
"Date",
"Status",
"CompanyName",
"CompanyReference",
"ApproverName",
"EntryQuantity"
],
"TimesheetStatusFilters": [
"Approved",
"Exported"
]
}
The Curl code is as follows if this helps:
curl --location --request POST 'myURL' \
--header 'APIKey' \
--header 'Content-Type: application/json' \
--data-raw '{
"StartDate": "2022-10-31T00:00:00",
"EndDate": "2022-10-31T00:00:00",
"ReportFields": [
"Date",
"Status",
"CompanyName",
"CompanyReference",
"ApproverName",
"EntryQuantity"
],
"TimesheetStatusFilters": [
"Approved",
"Exported"
]
}'
Iāve looked at Chris Webās post which Is very interesting and will try to adapt it but it looks challenging due to the way heās creating the content but in the meantime if you have any other ideas Iād be grateful. Many thanks again
Hi Imke,
Just to give you a little feedback - my API POST request is now working properly! The main thing that was needed was to format the content section properly - which meant changing some square "[" brackets for curly "{" ones. Many thanks for your assistance as it certainly helped guide me along the way.
Regards
Martin
Hi Imke,
One small (but potentially critical) issue was I noticed a syntax error on the GetAccessToken as I don't think it was actualy calling the function. I changed it to GetAccessToken() and the query runs but brings back an empty list. Perhaps I just need to 'play around' with the parameters now?
I've tried several formats and none of them seem tomake a difference although the parameters/fields are the same as the ones as I use in Postman which do bring back data.
One thought - Do I need to have both "content" and "query" if this is even possible?
Martin Short
Thanks, I'll give that a go
