powerquery - How do I add quotation marks to the beginning and end of a string or variable in Power Query M?

Question

This is the code I have:

let
    content = "{
        ""timezone"": ""tokyo standard time""
    }",
    Source = Json.Document(Web.Contents("localhost:7071/api/Function1", [Content=Text.ToBinary(content)]))
in
    Source

It works great. I want to be able to enter whichever timezone I want on the fly, however. I've tried something like this:

(Timezone) => 
let
    data = Text.Combine({"timezone", Timezone, ":"}),
    content = "{
        "&data&"
    }",
    Source = Json.Document(Web.Contents("localhost:7071/api/Function1", [Content=Text.ToBinary(content)]))
in
    Source

This almost works, but I need to add quotation marks around the "Timezone" input parameter/variable for it to be valid JSON.

I've tried using the Text.PadStart() and Text.PadEnd() functions but I can't get them to add the " character.

标签： powerquery

Answer 1

You've already found a solution yourself, but just thought I'd add that it might be better (in terms of readability and maintainability) to use Json.FromValue (details: https://docs.microsoft.com/en-us/powerquery-m/json-fromvalue).

Saves you having to imperatively build the JSON by hand and escaping/handling double quotes correctly. Example implementation in your case might be something like:

(Timezone) => 
    let
        body = Json.FromValue([timezone = Timezone]),
        Source = Json.Document(Web.Contents("localhost:7071/api/Function1", [Content = body]))
    in
        Source

Since Json.FromValue returns a value of type binary, you can pass it directly as Content field value (without needing to invoke Text.ToBinary).