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
ImkeF
Super User
Super User

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

MAS42
Frequent Visitor

Thanks, I'll give that a go

ImkeF
Super User
Super User

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

MAS42
Frequent Visitor

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

MAS42
Frequent Visitor

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

ImkeF
Super User
Super User

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

MAS42
Frequent Visitor

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”

MAS42_0-1669027146207.png

 

 

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

 

 

ImkeF
Super User
Super User

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

Anonymous
Not applicable

powerbi_error.pngI do that, but it says that it won't work. 

Anonymous
Not applicable

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

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

 

v-shex-msft
Community Support
Community Support

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thanks @v-shex-msft @ImkeF.

 

I have checked with API documentation. The information I had provided was given to me by api doc.

 

 

MyAPIdata.png

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

Anonymous
Not applicable

🙂 it says method not allowed.

error message.png

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

Anonymous
Not applicable

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?

 

image.png

 

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 @ImkeF,

 

by removing the surrounding commas behind the square brackets from query,  I am getting error inside the query statement as below:2017-08-08 14 17 58.png

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

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.