Forum Discussion
POST request and JSON body with several elements
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 SourceAlterntatively, 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""}}")
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
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""}}")ImkeF thank you so much! Both options work fine in basic form, but looks like M-syntax is easier to read. I also had problems adding parameter values (address etc) into the code with Text.ToBinary and found this to be easier with native M, since the query ultimately needs parameterized values.
I really hate bugging you more, but I ran into some more trouble when building up from the working base. How should I handle JSON objects with a colon in M? This is a snippet of JSON that works over Postman:{ "account": "https://myservice.com/api/tasks/accounts/123/", "contact": { "name": "task Name", "phone": "555-123" }, "address": { "raw_address": "10 greetings from Estonia" }, "metafields": { "account:sn": "123", "account:project": "PR1", "account:phase": "1" } }
I have an error in metafields section and apparently problem is the colon sign, e.g account:sn, account:project, account:phase objects. Do I somehow need to hide the colon since I'm getting an "invalid identifier" error message about "account:sn"Source = Json.Document(Web.Contents( "https://myservice.com/api/", [ Headers = [#"Authorization"= MyToken, #"Content-Type"="application/json"], Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", contact = [name = "taskName", phone = "555-123"], address = [raw_address = API_address], metafields = [account:sn = "123", account:project = "PR1", account:phase = "1"] ]) ] )), #"Converted to Table" = Record.ToTable(Source) in #"Converted to Table"Hi crossover ,
What you're typing inside Json.FromValue() is a what M calls a record. Records, as much as almost everything in PQ syntax, use identifiers.
So, on the left of each record element, a valid Identifier should be present.
account:sn is not valid because it contains a colon. Just turn that into #"account:sn" .
In case you'd like to learn almost everything about identifiers, here's a thorough explanation.Cheers,
Hi crossover ,
I believe you have to escape strings with colons like so: #"Text1:Text2"
Source = Json.Document(Web.Contents( "https://myservice.com/api/", [ Headers = [#"Authorization"= MyToken, #"Content-Type"="application/json"], Content= Json.FromValue([account = "https://myservice.com/api/tasks/accounts/123/", contact = [name = "taskName", phone = "555-123"], address = [raw_address = API_address], metafields = [#"account:sn" = "123", #"account:project" = "PR1", #"account:phase" = "1"] ]) ] )), #"Converted to Table" = Record.ToTable(Source) in #"Converted to Table"
