Trial #36: Making a PowerQuery (M) Http POST Request

Problem:

PowerBi makes it very easy for you to make http GET requests, which can sometimes be done simply from navigating through a GUI.

I talked about this process briefly in a previous blog.

However, some REST or other data APIs require a POST request for example a request with a JSON or XML body.

Solution:

I found a recommendation on the PowerBi Community page to add a Content parameter to the request.

It is worth referring to the Web.Conents() API to understand this.

As we can see Web.Conents has an optional Options parameter of record type. There are various options we can set here including the Header which itself a record type. Although it doesn’t say here, the Content parameter is of binary type, as explained by Imke Feldmann at the BICCOUNTANT. Therefore you must turn an arbitrary text value into binary with Text.ToBinary() or produce a JSON or XML response from table data.

Here is a basic an example function that performs a POST request with an XML body that includes a variable provided by a function parameter:

let GetReports = (Id as number) =>

    let
    //Parameters
        ClientSecret = "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    //Variables
        Url = "https://example.domain/resource?apiKey="& ClientSecret,
        Request = "<?xml version=""1.0"" encoding=""utf-8"" ?>
    <YourTag>
        <YourData meta="""& Number.ToText(Id) &""" Status=""1"" />
    </YourTag>",
           Source = Json.Document(Web.Contents(Url, [Content=Text.ToBinary(Request)]))
    in  Source

in GetReports

Pitfalls:

If you are working with string literals be careful to escape internal " by simply doubling the double quote "".

Ben Gribaudo has a great primer over at his blog on working with and escaping characters in strings.