POST http(s) request with xml content in Power Query

[patoduck]

Hi Guys,

1. The problem to solve: read stocks of a product
2. Target webpage= https://www.vinmonopolet.no/vmp/Land/Chile/Gato-Negro-Cabernet-Sauvignon-2014/p/295306
   1. Link to stock= "Finn butikk med varen på lager"  (Find a store with stocks) you need to click there.

 4. The code I'm using is here:

let
    Source =
Xml.Document(Web.Contents("https://www.vinmonopolet.no/vmp/store-pickup/295306/pointOfServices",

[Headers=[
Accept= "*/*",
AcceptEncoding= "gzip, deflate, br",
AcceptLanguage= "en-US,en;q=0.9,nb;q=0.8,es;q=0.7,ru;q=0.6",
Connection= "keep-alive",
ContentLength= "123",
ContentType= "application/x-www-form-urlencoded; charset=UTF-8",
Host = "www.vinmonopolet.no",
Origin = "https://www.vinmonopolet.no",
Referer = "https://www.vinmonopolet.no/vmp/Land/Chile/Gato-Negro-Cabernet-Sauvignon-2014/p/295306",
UserAgent= "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36",
XRequestedWith = "XMLHttpRequest"

]]))
in
    Source

But I'm getting the following error:

Expression.Error: The 'Host' header must be modified using the appropriate property or method.

Any ideas about how to solve it:

• Making and call to R scripts using Rseleniun (With docker) Difficult I will love to keep it simple and reproducible to other users.

BR

/Pato

[v-chuncz-msft]

@patoduck,

Just try to remove it from the query.

[patoduck]

You need to have it else you get 400 response. See below.

DataSource.Error: Web.Contents failed to get contents from 'https://www.vinmonopolet.no/vmp/store-pickup/295306/pointOfServices' (400): Bad Request
Details:
    DataSourceKind=Web
    DataSourcePath=https://www.vinmonopolet.no/vmp/store-pickup/295306/pointOfServices
    Url=https://www.vinmonopolet.no/vmp/store-pickup/295306/pointOfServices

[andreyminakov]

I see the same problem not only with Host, but with with Content-Type as well. I found this post regarding the problem - http://blogs.clariusconsulting.net/kzu/setting-http-headers-in-net-this-header-must-be-modified-usin...

And I get 400 responce without these parameters as well.

Possibly guys from Power Query team modify headers using not appropriate C class level ;-).

[andreyminakov]

I continued to investigate the case, and found that:

- Power Query automatically adds Host and Content-length headers. I watched that, using Whiteshark for sniffering traffic, created by Power Query. So the error, mentioned in the post, happens because:

1. we try to override the headers, wich is already prepared automatically, and

2. implementation of overriding these headers contains errors I mentioned previously.

But - possibly 2. is done intentionally, because we have no obvious reasons to override these 2 headers - automatically generated values meet the needs of the web-service in my case.

As for error 400 - in my case it ... disappeared today. I'm still try understand what happened, but at least my current understanding is that 400 responce is due to some mistakes in the service or in intermediators (protocol stack, Windows miracles etc.) and not due to mistakes in making call of the service from Power Query.

[andreyminakov]

AND - be careful - in my case, if you havigate to XML content of Web.Contents via UI of Power Query (so it adds Xml.Tables to Web.Contents), PQ automatically cut Headers clause from Web.Contents parameters, beleive it or not :-). And it happens in both Excel and PB Desctop PQs.