Forum Discussion
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 is Excel-centric and HTTP GET request already runs fine over PQ, so I was hoping to keep using the same toolset.
I found a nice video for POST requests over PowerQuery here: Making a POST Request Using Power Query - YouTube
Unfortunately it didn't help me far along as it just passes a single value to the API (Json.FromValue). Meanwhile I need to pass an entire JSON body. Not an enormous bunch of data, but still a handful of elements. I'd expect data to reside in Excel table or separate ranges, but not really sure how to go about converting it all into a single JSON body and pass onwards with POST request.
Would Python be a must here? Any thoughts on how to go about it? JSON body is something like this (I have run it via POSTMAN and it works fine).
{
"account": "https://myapi.com/id1/",
"contact": {
"name": "MyContact",
"phone": ""
},
"address": {
"raw_address": "Myaddress"
},
"description": "",
"complete_after": "2021-03-01T02:39:04.234330+02:00",
"state": "unassigned",
"metafields": {
"account:id1": "123",
"account:id2": "ABC",
}
}
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""}}")
6 Replies
Hi crossover ,
just create the nested object in Power Query and then use the Json.FromValue function to transform it into the format for your call: Easy POST requests with Power BI and Power Query using Json.FromValue – The BIccountant
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 SourceHello 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""}}")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"
