Forum Discussion

crossover's avatar
crossover
Advocate I
5 years ago
Solved

POST request and JSON body with several elements

HI! I'm in uncharted waters here, but hoping to find some help. I'm trying to create an end-user tool to run HTTP POST requests for entering new records in REST API over PowerQuery. The general usage...
Need Help
Show and Tell
  • ImkeF's avatar
    ImkeF
    5 years ago

    Hello crossover ,

    you have to use the M-syntax if you want to use Json.FromValue like so:

    let
    Source = Json.Document(Web.Contents(
           "https://myservice.com/api/tasks/",
           [
           
            Headers = [#"Authorization"= MyToken,
            #"Content-Type"="application/json"],
    
            Content=Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", address = [raw_address = "10 greetings from Estonia"]])       
           ]
    ))
in
    Source

     

    Alterntatively, the Text.ToBinary-function would would with the original JSON syntax like so:

    Content= Text.ToBinary("{""account"":""https://myservice.com/api/tasks/accounts/123/"",""address"":{""raw_address"":""10 greetings from Estonia""}}")
crossover's avatar
crossover
Advocate I
5 years ago

Hello ImkeF and others!


Thanks for the input. Unfortunately I'm still struggling to get it to work. I have teared my POST request JSON to a bare minimum that API accepts via Postman and I'm trying to build on that in PowerQuery:

 

{
  "account": "https://myservice.com/api/tasks/accounts/123/",
  "address": {
    "raw_address": "10 greetings from Estonia"
  }
}

 

 

This is what I came up with in PowerQuery, however PQ doesn't even launch the query and points towards the colon in front the "account": (comma expected). When replacing all colons with commas, I end up with Error 400 (Bad request). Am I missing something very minor here or is much more extensive nesting needed? In practice, I'd need to compile JSON maybe 3-4x as long.

 

let
    Source = Json.Document(Web.Contents(
           "https://myservice.com/api/tasks/",
           [
           
            Headers = [#"Authorization"= MyToken,
            #"Content-Type"="application/json"],
    
            Content=Json.FromValue({"account":"https://myservice.com/api/tasks/accounts/123/","address":{"raw_address":"10 greetings from Estonia"}})       
           ]
    ))
in
    Source