Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
If you click on "Show Error": Is this what you get?
Please send full code.
Please also send link to relevant FB-api-doc.
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
got my query working.
Thanks everyone.
Agree with above link and solution
let
Content = "{
"methodType": "details",
"pageNum": "0",
"pageSize": "3",
"searchParameter": [
{
"key": "order_number",
"value": "469584336"
},
{
"key": "global_bu_id",
"value": "11"
}
],
"sourceApplication": "MORC"
}",
Source = Json.Document(Web.Contents("https://goss-csvc-prod.us.mac.com/gossv3/purchase/search/",[Content=Text.ToBinary(content)]))
in
Source
Getting error "Token comma expected"
Hello @navneet0512 ,
you must escape the quotes and also regard that M is case sensitive. So the correct code would look like so:
let
Content = "{
""methodType"": ""details"",
""pageNum"": ""0"",
""pageSize"": ""3"",
""searchParameter"": [
{
""key"": ""order_number"",
""value"": ""469584336""
},
{
""key"": ""global_bu_id"",
""value"": ""11""
}
],
""sourceApplication"": ""MORC""
}",
Source = Json.Document(Web.Contents("https://goss-csvc-prod.us.mac.com/gossv3/purchase/search/",[Content=Text.ToBinary(Content)]))
in
Source
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,
I am trying to connect Power BI to the Spotify API, but somehow struggling to get it work.
Trying to use some code of this thread, but ending up with the following error message:
DataSource.Error: Web.Contents failed to get contents from 'https://accounts.spotify.com/api/token?grant_type=client_credentials' (405): Method Not Allowed
Details:
DataSourceKind=Web
DataSourcePath=https://accounts.spotify.com/api/token
Url=https://accounts.spotify.com/api/token?grant_type=client_credentials
The code I used was:
let
url="https://accounts.spotify.com/api/token?grant_type=client_credentials",
auth_key = "BASIC <Base64encoded clientID and secret",
header= [#"Authorization" = auth_key,
#"Content-Type" = "application/json; charset=utf-8"],
webdata = Web.Contents(url , [Headers=header]),
response = Json.Document(webdata)
in
response
Any suggestions on how to make this work?
Thanks!!!
Hi @Anonymous. could you please share your code here?
I am having trouble with my API too.
There you go @DN
let auth_key ="Basic FNekV5WHpNNVpJReFl6VTNZalJpNTM3ZTVhZmEzNTc5YzUxNTlmNDg0N2QzNWI=", base_url = "https://api.testwebsite.com/", extension = "0/facebook/metrics", url = base_url &extension, header= [#"Authorization" = auth_key, #"Content-Type" = "application/json; charset=utf-8"], content1= "{ ""date_start"":""2017-10-01"", ""date_end"":""2017-10-30"", ""profiles"":[""id1"",""id2""], ""metrics"":[ ""fans_change"",""fans_count_lifetime"", ""insights_engaged_users_count"", ""insights_impressions_by_age_gender_unique"",""insights_impressions"",""insights_impressions_by_paid_non_paid_unique"", ""insights_negative_feedback"",""insights_positive_feedback"" ] }", webdata1 = Web.Contents(url, [Headers=header,Content = Text.ToBinary(content1)]), response1 = Json.Document(webdata1) in response1
thanks a lot for your reply!
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... user:password)
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"
@Anonymous Great if you managed to make it work.
yes, I also changed my user:pass in to 64encode externally and then entered in M.
My API only returns a limited data, so now I am working to paginate it, recursively.
nice! once you get it paginated correctly, please share the pagination code here.
thanks!
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-Dro...
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.
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
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 |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |