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

Question

&nbsp;Hi Guys,The problem to solve: read stocks of a productTarget webpage=&nbsp;https://www.vinmonopolet.no/vmp/Land/Chile/Gato-Negro-Cabernet-Sauvignon-2014/p/295306Link to stock= "Finn butikk med varen på lager"&nbsp; (Find a store with stocks) you need to click there.&nbsp;&nbsp;&nbsp;4. The code I'm using is here:&nbsp;let&nbsp;&nbsp;&nbsp; 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"&nbsp;]]))in&nbsp;&nbsp;&nbsp; Source&nbsp;&nbsp;&nbsp;But I'm getting the following error:&nbsp;Expression.Error: The 'Host' header must be modified using the appropriate property or method.&nbsp;&nbsp;Any ideas about how to solve it:&nbsp;Making and call to R scripts using Rseleniun (With docker) Difficult I will love to keep it simple and reproducible to other users.&nbsp;BR&nbsp;/Pato&nbsp;&nbsp;

andreyminakov · Answer

I continued to investigate the case, and found that:- Power Query automatically adds Host and&nbsp;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, and2. implementation of overriding these headers contains errors I mentioned previously.&nbsp;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.&nbsp;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.&nbsp;

v-chuncz-msft · Answer

patoduck,
&nbsp;
Just try to remove it from the query.

patoduck · Answer

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 · Answer

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-using-the-appropriate-property/

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 · Answer

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

Forum Discussion

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

5 Replies

Related content

The content is blocked.

web content

Web.Contents failed to get contents (400) bad request error

Feature Request: Matrix Visual

This content is blocked. Content the site owner to fix the issue