Forum Discussion

digiroo's avatar
digiroo
Frequent Visitor
9 years ago

M Query to use POST method on a Web API

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”,[Content=Text.ToBinary(PostContents)]),

 

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-sweden/

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

 

Need Help

10 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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