M Query to use POST method on a Web API

[digiroo]

Hi I'm trying to get a POST method to work on the Sage One API. 

GET method works fine, POST method requires the web.content,[Contents] option. Im trying to work through the options.

From various posts, there appear to be a number of Content-Types:

ContentTypes:

Not Specified/Default:

PostContents= “{

“”query””: [

{

“”code””: “”Kon””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”1″”,

“”2″”

Source = Web.Contents(“http://api.scb.se/OV0104/v1/doris/sv/ssd/START/BE/BE0101/BE0101A/BefolkningNy...

multipart/form-data:

body = Text.Combine(List.Transform(Record.FieldNames(parts), each item(_, Record.Field(parts, _)))) & boundary & "--" & crlf

Headers=[#"Content-Type"="multipart/form-data],

Content=Text.ToBinary(body)

application/x-www-form-urlencoded

#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],

Content = Text.ToBinary("grant_type=client_credentials")

Posts:

https://eriksvensen.wordpress.com/2014/09/15/specifying-json-query-in-power-query-example-statistics...

https://gist.github.com/CurtHagenlocher/b21ce9cddf54e3807317

https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/

My first question:

Is there any documentaion on the options for Content-Type i.e.

• Default
• multipart/form-data
• application/x-www-form-urlencoded

For the default option, the syntax/format used is:

PostContents= “{

“”query””: [

{

“”code””: “”Kon””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”1″”,

“”2″”

My second question, for the default/unspecified Content-Type is there any documentation on the parameters:

• Code
• Selection
• Filter
• Values

and specifically how to structure this in M?

Thanks

Digiroo

[Eric_Zhang]

@digiroo

The PostContents is JSON format, try to specify 

Headers=[#"Content-Type"="application/json"]

Regarding more details how to call the Sage One API, you shall go through the specific documentation. Using GET/POST and what content in the body is clarified in the doucumentation.

Anyway, you can check a sample to send a POST request(create dataset) of the Power BI REST APIs. The highlighted part in the picture is expected.

let
    AuthKey = "Bearer youTokenHere",
    url = "https://api.powerbi.com/v1.0/myorg/datasets",
    body = "{""name"": ""SalesMarketing2"",""tables"":   
    [{""name"": ""Product"", ""columns"":   
        [{ ""name"": ""ProductID"", ""dataType"": ""Int64""},  
         { ""name"": ""Name"", ""dataType"": ""string""},  
         { ""name"": ""Category"", ""dataType"": ""string""},  
         { ""name"": ""IsCompete"", ""dataType"": ""bool""},  
         { ""name"": ""ManufacturedOn"", ""dataType"": ""DateTime""}  
        ]  
      }  
    ]  
}",
    Source = Json.Document(Web.Contents(url,[
             
         Headers = [#"Authorization"=AuthKey ,
                    #"Content-Type"="application/json"],
         Content = Text.ToBinary(body) 
             ]   
        ))
in
    Source

[Anonymous]

Hello @Eric_Zhang,

I'm trying to implement such request on Clockify API.

https://docs.clockify.me/#tag/Time-Entry-Report/operation/generateSummaryReport

= let
#"URL" = "https://reports.api.clockify.me/v1/workspaces/63be77bd5e398c3868773063/reports/summary",
#"body" = "{
""dateRangeStart"": ""2023-01-01T00:00:00.000Z"",
""dateRangeEnd"": ""2023-12-31T23:59:59.000Z"",
""summaryFilter"": {""groups"": [""USER""]},
""exportType"": ""JSON"",
"users": {
    "ids": ["63be77bd5e398c3868773062","637379b66c975d7d01ebceea","64104151cd49c235581406fb","63c54ec6d6bbe005e2c42677"],
""contains"": ""CONTAINS"",
""status"": ""ALL""
}
}",
#"Parsed_JSON" = Json.Document(#"body"),
#"BuildQueryString" = Uri.BuildQueryString(#"Parsed_JSON"),
Source = Json.Document(Web.Contents(#"URL",
[Headers=[Accept="application/json", #"x-api-key"="XXXX"], Content = Text.ToBinary(#"body")])),
#"Converted to Table" = Table.FromColumns({Source})
in
Source

"X-API-KEY"="XXXX" where XXXX is my API Key 

I think i'm following what you have done, but i get an 415 error. 

DataSource.Error : Web.Contents n'a pas réussi à obtenir le contenu de « https://reports.api.clockify.me/v1/workspaces/63be77bd5e398c3868773063/reports/summary » (415) : Unsupported Media Type
Détails :
    DataSourceKind=Web
    DataSourcePath=https://reports.api.clockify.me/v1/workspaces/63be77bd5e398c3868773063/reports/summary
    Url=https://reports.api.clockify.me/v1/workspaces/63be77bd5e398c3868773063/reports/summary

But on Postman, it works as a charm :

I don't get what is the meaning of 415 error and why i get it. 

Would be really helpful if you can help. 

Kind regards 

Fabien

[Anonymous]

#"Content-Type" = "application/json" was missing in the header ... 😕 

Solved

[Anonymous]

Hello,

Is there anyone that could help ? I compared what I have done with https://developers.de/2021/10/11/how-to-send-the-post-request-in-powe-query/ and it seems identic. 

Kind regards

[digiroo]

.

[digiroo]

Hi,

Thanks for the response.

I have the Sage One documentation and have updated my query to:

let

body=
"{
  ""FromDate"": ""2017-01-01"",
  ""ToDate"": ""2018-01-01""
}",

url = "https://accounting.sageone.co.za/api/1.1.2/AccountBalance/Get?ApiKey={GUID-GUID-GUID}&CompanyId=1234...",

Source = Json.Document(Web.Contents(url,[ Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] ))

in Source

but Im getting this error:

DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
    DataSourceKind=Web
    DataSourcePath=https://accounting.sageone.co.za/api/1.1.2/AccountBalance/Get

Can you advise?

Thanks

Digiroo

[Eric_Zhang]

---

@digiroo wrote:

Hi,

Thanks for the response.

I have the Sage One documentation and have updated my query to:

let

body=
"{
  ""FromDate"": ""2017-01-01"",
  ""ToDate"": ""2018-01-01""
}",

url = "https://accounting.sageone.co.za/api/1.1.2/AccountBalance/Get?ApiKey={GUID-GUID-GUID}&CompanyId=1234...",

Source = Json.Document(Web.Contents(url,[ Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] ))

in Source

but Im getting this error:

DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
    DataSourceKind=Web
    DataSourcePath=https://accounting.sageone.co.za/api/1.1.2/AccountBalance/Get

Can you advise?

Thanks

Digiroo

---

That seems the limitation of Power Query, check this link https://social.technet.microsoft.com/Forums/en-US/6366dd5d-302c-42fb-8283-c833954c3858/sending-conte...

Isn't there any authentication key(#"Authorization"=AuthKey in header) for the API? If not, what's going on when setting an empty Authorization?

[Anonymous]

did you make it work?

could you please share your code here? I am also having trouble on my API.

[digiroo]

I can see a reported bug on the Power BI Support page.

Users are unable to edit credentials for data sources in reports and unable to refresh the datasets. Power BI team is aware of the issue and are working on a fix. Next update 01/18/2017 09:30 PST.

Is this issue related?

[marco_lima]

let

    Source = Xml.Tables (Web.Contents("http://webapi", [Content=Text.ToBinary("")]))

    in Source

In this case for XML if you know how to do it in Json or Csv it would be nice to show as well.