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,620 points
1,555 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,490 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
• 18,570 points
1,309 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
• 18,570 points
790 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
• 52,360 points
4,109 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,620 points
2,396 views
0 votes
1 answer

Format Data to tabular structure in Excel - Power m Query - Excel

Hi, @Kiranbvsn You can check this out: https://www.howtoexcel.org/ ...READ MORE

answered Oct 27, 2020 in Power BI by Gitika
• 65,910 points
433 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,160 points
5,838 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,160 points
3,789 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
4,209 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,490 points
3,226 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, 2019 in Power BI by Shubham
• 13,490 points
1,927 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP