Posting table data to a web API using PowerQuery in Excel

So we have this Excel document, that extensively relies on data from an API. However, we wanted the option to send data to the API as well, to update it with values added in Excel. This is a horrible idea, but while the web guys are making a pretty front end for this, I can make a temporary solution with PowerQuery.

There are a couple of rules in Excel you need to observe to accomplish this:

1. Excel can’t use its built-in authentication while making a POST request. If you need this, rather than an “ApiAuth” data string as I use, you’ll have to make a GET request and put the data in the query string.
2. You have to “use” the output of the web request in your resulting PowerQuery output, or Excel won’t make the request at all. I get around this by adding the returned string (“OK”) from the API endpoint to a new column, and then just removing that column at a later stage.

Now, the code:

#"Changed Type" = <previous step here>,
PayloadForWebService = #"Changed Type",
CsvHeader = Text.Combine(Table.ColumnNames(PayloadForWebService), "|"),
CsvData = List.Transform(
    Table.ToRecords(PayloadForWebService),
    each Text.Combine(
        List.Transform(Record.FieldValues(_), (value) =>
            if (Type.Is(Value.Type(value), type date) or Type.Is(Value.Type(value), type datetime)) then
                Date.ToText(value, "yyyy-MM-dd")
            else if (Type.Is(Value.Type(value), type number)) then
                Number.ToText(value, "0.############", "en-US")
            else
                Text.From(value)
        ),
        "|"
    )
),
CsvString = Text.Combine(List.Combine({ {CsvHeader}, CsvData }), "#(lf)"),
ApiResponse = Text.FromBinary(
    Web.Contents(
        ApiBase & "excel/mytable/year/" & Text.From(Date.Year(CurrentYear)),
        [ Headers = [ Authorization = ApiAuth ], Content = Text.ToBinary(CsvString) ]
    ),
    TextEncoding.Utf8
),
#"Required Use of API response" = Table.AddColumn(#"Changed Type", ApiResponse", each ApiResponse, type text),
#"Removed API response" = Table.RemoveColumns(#"Required Use of API response", {"ApiResponse"})

A couple of key points: (read if you’re having trouble)

1. #”Changed Type” is my previous step. It can be whatever you have, at the point in your query where you want to submit the data.
2. CsvHeader is the pipe separated header for the CSV. I use a pipe, as that will never appear in the data, but you can use whatever works for you, including multiple characters or something like “$eparator|!|”, as long as it’s supported on the web service end.
3. CsvData is the pipe separated values for each row. There’s some specific parsing in there to force yyyy-MM-dd date formatting and period for decimals in the CSV output. Add whatever is appropriate for your data.
4. CsvString is the final payload string, to be sent to the web service. You can wrap this further in JSON or something if you need to. Also, I use a newline (#(lf)) as the record separator. If your data has newlines in it, use something else. Isn’t manually writing CSV fun? 😀
5. ApiResponse is whatever the API returns, parsed as a Utf8 string
   1. In this step I use ApiBase, which is a variable stored as another query, for easy modification across all queries. You can replace it with something like “https://my.service/api” if you don’t do this.
   2. I also use CurrentYear and ApiAuth, which are the same thing. Again you can replace them with hardcoded strings, or not include them if your API does not require it. My data happens to be for a given year, and my API requires an authentication token, so I have these in there. Adapt to your use case.
6. #”Required Use of API response” is just what it says on the can – If you don’t use the output from the web request, Excel will simply ignore all your hard work and not make the call. PowerQuery in Excel is not designed to push data to web services (duh!).
7. #”Removed API response” is just there to remove step 6, since we don’t actually want the data. Excel will still think you’re using the output and make the call.

I hope you never have to do this, but if you do, I hope this post helps. Good luck.