Help : Create POST api request using double quotes in body to format payload

[CatParky]

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
    Source

This 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 ?

[artemus · Accepted solution]

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.

[CatParky]

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

[CatParky]

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.