How to send a Post query using Excel Power Query

Question

I am trying to retrieve data from the Betfair API and have to send some extra info as headers and Json data. Details below:

Url:

https://api.betfair.com/exchange/account/json-rpc/v1

Headers:

{ 'X-Application' :'exampleappid', 'X-Authentication' :'examplesessionkey','Content-Type':'application/json' }

formdata:

{"jsonrpc": "2.0","method": "AccountAPING/v1.0/getAccountStatement", "params": {"itemDateRange":{},"includeItem":"ALL"}, "id": 1}

Currently in excel I go to the Data tab then click on New Query>From Other Sources>From Web>Advanced then I type in the url and the headers in the respective fields. Where do I put in the other form data, or its just not possible?

Edit:

I have created the following query in the query editor :

let
    formdata = "{""jsonrpc"": ""2.0"",""method"": ""AccountAPING/v1.0/getAccountStatement"", ""params"": {""itemDateRange"":{},""includeItem"":""ALL""}, ""id"": 1}" ,

    Source = Web.Contents("https://api.betfair.com/exchange/account/json-rpc/v1",[Headers= [#"X-Application"="appkey", #"X-Authentication"="sessionkey", #"Content-Type"="application/json"],
Content=Text.ToBinary(formdata)])


in
    Source

and I get the following error DataSource.Error: The server committed a protocol violation. Section=ResponseHeader Detail=Header name is invalid Details: https://api.betfair.com/exchange/account/json-rpc/v1

Wedge Wedge · Accepted Answer · 2019-09-23T16:03:16

Unfortunately I don't think there is a field in the UI for web connections to set the formdata, but it can be done by manually setting up the connection. I think what you have without the formdata would look like this in the M code

Web.Contents(
    "https://api.betfair.com/exchange/account/json-rpc/v1", 
    [Headers=[#"X-Application"="exampleappid", #"X-Authenticaion"="examplesessionkey", #"Content-Type"="application/json"]]
    )

There is a "Content" field that can be added after the Headers. In most examples I've seen (and I recall doing this myself once though I don't remember what for), you setup a string for the form data and use Text.ToBinary before passing it into the Content field. So a full query would look something like this:

let
formdata = "{""jsonrpc"": ""2.0"",""method"": ""AccountAPING/v1.0/getAccountStatement"", ""params"": {""itemDateRange"":{},""includeItem"":""ALL""}, ""id"": 1}" ,
Source = 
Web.Contents(
    "https://api.betfair.com/exchange/account/json-rpc/v1", 
    [
    Headers=[#"X-Application"="exampleappid", #"X-Authenticaion"="examplesessionkey", #"Content-Type"="application/json"],
    Content = Text.ToBinary(formdata)
    ]
    )

in
    Source

How to send a Post query using Excel Power Query

1 Answers