Power Query Web request results in CR must be followed by LF Error

Question

When you use the Web.Page(Web.Contents('url')) function to read a table from a web page, some sites will cause an error due to inconsistent linefeeds.

DataSource.Error: The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF

There doesn't appear to be an option you can pass to the Web functions to ignore those errors.

This method works for a short while, but doesn't survive a save/refresh:

let
   BufferedBinary = Binary.Buffer(Web.Contents("http://vote.sos.ca.gov/returns/president/party/democratic/county/all")),
   CleanedUp = Text.Replace(Text.Replace(Text.FromBinary(BufferedBinary), "#(cr,lf)", "#(lf)"), "#(lf)", "#(cr,lf)"),
   Table = Web.Page(CleanedUp)
in
   Table

Answer 1

What I think is, it might look like the other code sample works, but that probably means it just hasn't been run yet.

The two library functions Web.Contents('url') and Web.Page(Web.Contents('url')) use different HTTP clients.

Web. Contents uses a pretty basic HTTP client, but Web. Page over a non-transformed Web. Contents will instead use the IE browser to load the URL directly. IE is very forgiving for any HTTP protocol errors, but the basic HTTP client is much more strict, which causes the error you see.

By calling Binary.Buffer or Text. Replace in "between," that will skip the optimization where Web. The page uses the browser directly, and it instead uses Web. Contents to make the web request and then load those bytes into the browser.

So in this case, if you invoke

Web.Page(Web.Contents("http://vote.sos.ca.gov/returns/president/party/democratic/county/all"))

You'll get a good result table, but "From Web" will be broken because of the Web.Contents('url') by itself will error.

PS. our HTTP client library doesn't give very good error messages, there's nothing wrong CR or LF characters. Even if there was, there's nothing that Text. Replace over the body can't fix in this case because the problem is in the HTTP headers.