Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
got my query working.
Thanks everyone.
Hi @MAS42 ,
I would recommend to use a web traffic tracking tool like Fiddler to compare both calls made from Postman and Power BI. This should give you an idea which part is wrong in PBI.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks, I'll give that a go
Hi @MAS42 ,
does the GetAccessToken (in AuthKey) really return a string or might it be a function that requires to be called with a/some parameters?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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
response
Otherwise 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....
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I do that, but it says that it won't work.
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
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
Hi @Anonymous,
As ImkeF said, you should check your api document first.
Power bi support a lot of methods to push parameters: for e.g. url, header, form data, etc... (It will based on authorization rules and api define.)
Regards,
Xiaoxin Sheng
Thanks @v-shex-msft @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
response
Can someone please help where I am going wrong 😞
What does the error message say? 😉
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
🙂 it says method not allowed.
I was wondering if it was because there is something worng in my syntex or I am calling the query parameters from some incorrect place.
Because, as I pasted the documentation from the api, it does allow these values, and these have to be enquired using these query parameters.
I was concerned about the general query format. Does that make sense to you, ie, combining header(which include authorization info) and query (which has got optional paramters) as in below statement
webdata = Web.Contents(url, [Headers=header,Query = query]),
response = Json.Document(webdata)
Below is my complete statement
url=myurl
auth_key = "BASIC mykey"
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
response
please try this code instead:
url=myurl, auth_key = "BASIC mykey", 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 response
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi All,
I am jumping into this converstion because having something similar problem wherein I am trying to use GraphQL POST API to get the data in power BI.
I used source as web and then advance power query editor.
let
body = Text.ToBinary("{""query"": ""{
packMarketPent(periods: {start_date: \""2019-01-01\"", end_date: \""2019-10-01\""}){
channel
service_name
metric_absolute_actual
}
}""
}" ),
actualUrl = "https://*******.com/api/v1/pack_mkt",
Options = [
Headers=[#"Content-Type"="application/json"],
Content= body
],
result = Json.Document(Web.Contents(actualUrl, Options))
in
result
but I am getting error
DataSource.Error: Web.Contents failed to get contents from 'https://uat*****.com/api/v1/post_api' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://******.com/api/v1/post_api
Url=https://**************.com/api/v1/pos_api
Please help here, What I should pick as data source and in code What is wrong.
when I am using this API in browser it is working and postman also
Hi @Anonymous ,
Have you tried with a hardcoded body?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF,
by removing the surrounding commas behind the square brackets from query, I am getting error inside the query statement as below:
Sorry, my bad: There should be no double "", but just single ". Pls try
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |