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

0 votes

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
Oct 29, 2018 in Power BI by Upasana
• 8,550 points
188 views

1 answer to this question.

0 votes

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.

answered Oct 29, 2018 by Shubham
• 13,310 points

Related Questions In Power BI

0 votes
1 answer

How many reports can be created in one Power BI reports

You have a limit of 1000 datasets ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 16,710 points
59 views
0 votes
1 answer

Dax query in power bi

Here is my table before I have ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 16,710 points
94 views
+1 vote
2 answers

403 error thrown by power bi report server when trying to get list of data sets

Hey @saranya, Try removing the header and ...READ MORE

answered Oct 29, 2018 in Power BI by Kalgi
• 42,510 points
222 views
0 votes
1 answer

Error:connecting to Azure SSAS server in Power-BI through Import mode

If I understand correctly, you're connecting to ...READ MORE

answered Nov 9, 2018 in Power BI by Upasana
• 8,550 points
346 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
496 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
318 views
0 votes
1 answer

How can I search for multiple strings?

A simple solution is this: List.ContainsAny(Text.SplitAny("This is a test ...READ MORE

answered Oct 24, 2018 in Power BI by Upasana
• 160 points
203 views
0 votes
1 answer

Power BI : npgsql installation

As a general rule Npgsql can be ...READ MORE

answered Nov 27, 2018 in Power BI by Shubham
• 13,310 points
243 views
0 votes
1 answer

Select Columns from table instead of removing afterwards in Power Query

Try this. let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = ...READ MORE

answered Oct 31, 2018 in Power BI by Shubham
• 13,310 points
311 views
0 votes
1 answer

To calculate conditional running total in power query

Try Table Buffer after adding the index, or ...READ MORE

answered Apr 26 in Power BI by Shubham
• 13,310 points
257 views