Forum Discussion
Help : Create POST api request using double quotes in body to format payload
Hi,
I'm trying to write a query that will format and POST an excel table contents to a Knack database using their api.
So far I have the following query using the custom GetJson function from Chris Webb's Blog Post
let
AppID = "REDACTED",
AuthKey = "REDACTED",
url = "https://api.knack.com/v1/objects/object_38/records",
TableSource = Excel.CurrentWorkbook(){[Name="TableUpload"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(TableSource),
#"Removed Top Rows" = Table.Skip(#"Demoted Headers",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{"field_417", type date}),
body = GetJson(#"Promoted Headers"),
//Construct the POST query
Source = Json.Document(Web.Contents(url,[
Headers = [#"X-Knack-Application-Id"=AppID,
#"X-Knack-REST-API-Key"=AuthKey,
#"Content-Type"="application/json"],
Content = Text.ToBinary(body)
]
))
in
SourceThis gives me a json string :
[{"field_1833":1,"field_1832":"BS_20191211","field_488":"Bob Smith","field_417":"2019-12-01","field_418":"Test Description","field_419":"61120 Software & IT","field_420":"GBP","field_421":null,"field_422":null,"field_423":10,"field_424":"Yes","field_425":2,"field_427":12,"field_429":"Notes Test"}]
The content of the string doesn't really matter for the purposes of this question... it's the formatting I'm having issues with. The query sends data to Knack but it's not sending correctly as the fields are showing as empty.
On testing I replaced the body variable with some hardcoded values :
body = "{ ""field_1832"": ""AAAAA"", ""field_1833"": ""5""}",Which then created the json string :
{ "field_1832": "AAAAA", "field_1833": "5"}
and populated the fields as needed
This leads me to believe that everything should be enclosed in quotes when sending the json payload. Powerquery does not want me manually concatenating quotes to the fields as it adds backslashes :
[{"\"field_1833\"":1,"\"field_1832\"":"CP_20191211","\"field_488\"":"Catherine Parkinson","\"field_417\"":"2019-12-01T00:00:00","\"field_418\"":"Description","\"field_419\"":"61120 Software & IT","\"field_420\"":"GBP","\"field_421\"":null,"\"field_422\"":null,"\"field_423\"":10,"\"field_424\"":"Yes","\"field_425\"":2,"\"field_427\"":12,"\"field_429\"":"Notes Test"}]
So my question is how can I format my payload without haveing the backslashes ?
I think the issue is that you are double formatting the data.
Remove:
body = GetJson(#"Promoted Headers"),change
Content = Text.ToBinary(body)to
Content = Json.From(#"Promoted Headers")And hopefully that should work.
Actually, looking into this further. You need to use a single row in a table, not the entire table... E.g.
change
Content = Json.From(#"Promoted Headers")to
Content = Json.From(#"Promoted Headers"{0})if you just want to use the first row of the table.
3 Replies
I think the issue is that you are double formatting the data.
Remove:
body = GetJson(#"Promoted Headers"),change
Content = Text.ToBinary(body)to
Content = Json.From(#"Promoted Headers")And hopefully that should work.
Actually, looking into this further. You need to use a single row in a table, not the entire table... E.g.
change
Content = Json.From(#"Promoted Headers")to
Content = Json.From(#"Promoted Headers"{0})if you just want to use the first row of the table.
Thank you so much for your help. Using your suggestion I get the error :
Expression.Error: The name 'Json.From' wasn't recognized. Make sure it's spelled correctly.
I tried
Content = Json.FromValue(#"Promoted Headers"{0})And got the same view of the fields with no values.
My error !
I skip read the part about removing
body = GetJson(#"Promoted Headers"),I also updated to Jason.FromValue and it's worked @:
Source = Json.Document(Web.Contents(url,[ Headers = [#"X-Knack-Application-Id"=AppID, #"X-Knack-REST-API-Key"=AuthKey, #"Content-Type"="application/json"], Content = Json.FromValue(#"Promoted Headers"{0}) ] )) in Source
