POST request strange behavior - request is sent twice with EXPECT header added

[cleon169]

I've created the code below in Power Query to send a POST request to an API server that contains report parameters in the body. The authentication protocol is OAuth 1.0 single-legged (i.e. tokens aren't used). The API server then generates a response with a csv report in the body of the response. The API POST was developed in Postman and works without issue there. However it doesn't work in Power Query and I get a 500 reponse error.

let

epoch = Number.ToText(Duration.TotalSeconds(DateTimeZone.UtcNow() - #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0))),
nonce = Number.ToText(Duration.TotalSeconds(DateTimeZone.UtcNow() - #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0))*10),

Source = Web.Contents("https://api.com/v1/ac48e092/reports",
[Headers=[Authorization="OAuth oauth_consumer_key=""xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"",
oauth_signature_method=""PLAINTEXT"",oauth_timestamp="""&epoch&""",oauth_nonce="""&nonce&""",
oauth_version=""1.0"",oauth_signature=""xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx""",
#"Content-Type"="application/json"],
Content=Text.ToBinary("{""id"": ""general_ledger_detail"",
	  ""start_date"": ""2018-08-01"",
	  ""end_date"": ""2018-08-10"",
	  ""report_format"": ""csv""
	}")]),
Result = Csv.Document(Source)

in
Result

I sniffed the Power Query activity with Fiddler and it looks that the Power Query is adding the header “Expect: 100-Continue” to the headers that I’m defining for the authentication and content type. It’s then waiting for a 100 response from the API server, but the API server is ignoring this and just sending the report payload instead the 100 response. Then Power Query is handling that as the response to the Expect: 100-Continue and resending the original Post request a second time.

Fiddler Raw PQ Request:

POST https://api.com/v1/ac48e092/reports HTTP/1.1
Authorization: OAuth oauth_consumer_key="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",oauth_signature_method="PLAINTEXT",oauth_timestamp="1539292503.3093414",oauth_nonce="15392925033.098366",oauth_version="1.0",oauth_signature="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
Content-Type: application/json
Accept: */*
User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
Accept-Encoding: gzip, deflate
Host: api.com
Content-Length: 120
Expect: 100-continue
{"id": "general_ledger_detail",
	  "start_date": "2018-08-01",
	  "end_date": "2018-08-10",
	  "report_format": "csv"
	}

Fiddler traffic showing 2 identical requests being sent by Power Query

 The problem is that the 2nd request that Power Query is sending has identical headers as the 1st request, so the timestamp and nonce value is getting duplicated a 2nd time (these should be unique with each request). The API server sees this second request as an invalid authentication header and sends a 500 response instead of the report payload.

Has anyone else encountered this behavior? Are there any solutions to this? Unfortunately this web service doesn't have a GET method to generate these .csv reports, so I'm stuck having to use POST.

[blopez11]

I am seeing the same thing.  Did you ever by chance able to resolve this?

Thanks,

[cleon169]

I never found a good answer on this. However I was able to get it to work for me.

I replaced the line 

Result = Csv.Document(Source)

with

    #"Imported Text" = Table.FromColumns({Lines.FromBinary(Source,null,null,1252)}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Imported Text", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13"}),

 Change the #"Split Column by Delimiter" variables to match the number of delimited columns in your data.

PQ still sends multiple requests for the same item, however it appears to revaluate the variables and the API server throw an error.

Hope this helps! Let me know if it does.

[blopez11]

Thanks for the reply

Mine is slightly different, where I am reading XML from the response

But the thing is the web service we are using, apparently doesn't like the two back to back POSTcalls, and for the second, just replies back with an empty content body

So my report ends up with nothing:)

There doesn't appear to be a solution to this