Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable
34 REPLIES 34
Anonymous
Not applicable

@ImkeF and that brings us to the following 😞

 

2017-08-09 11 33 13.png

 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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!!!

Anonymous
Not applicable

Hi @Anonymous. could you please share your code here?

I am having trouble with my API too.

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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
Not applicable

@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.

Anonymous
Not applicable

nice! once you get it paginated correctly, please share the pagination code here.

thanks!

ImkeF
Super User
Super User

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

MAS42
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.