Forum Discussion

Joe Blackburn's avatar
Joe Blackburn
Copper Contributor
Jul 23, 2018

Data From Web - Connect Error - CR must be followed by LF

I am trying to use Office 365 Get & Transform data from web. When I try to connect, it errors out with attached message.
If I use the older "Legacy Add-In" in Office 365, it connects fine, and captures results. If I use Office 2013 it works fine with the same URL.
Can anyone help me to get the newer Data from Web (Get & Transform) working?

I'm connecting to a secure search result page on Ancestry.com:
https://tinyurl.com/ycdlnylo
I've also tried with the full, long URL.
The CR/LF message has a long history, and occurs in different situations, but I couldn't find a solution  that matched Excel Get & Transform at all...

BI & Data Analytics
Need Help

5 Replies

    • Joe Blackburn's avatar
      Joe Blackburn
      Copper Contributor
      Jul 23, 2018

      Thank you, Sergei

      I just tried this both using authentication and anonymous. Didn't work, same error as before.

      Also - where is there a description of the HTTP request header parameters (that are in the drop on the Advanced "From Web" form)?
      Something that tells what the allowable options are?

      The spurious LF in Cookies thing has been know for a long time. But here's the thing - the error does not occur in the Legacy "From Web" Add-in - only in the newest version of Get & Transform - which seems to have re-broken the function?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 23, 2018

        Joe,

         

        Attached is the file with connection on first table, I used anonymous - it works. Generated query is

        let
    Source = Web.Page(Web.Contents(
            "https://search.ancestry.com/cgi-bin/sse.dll?gl=allgs&gsln=Blackburn&catBucket=r&cp=4&_83004003-n_xcl=f&msbdy=1610&msbpn__ftp=norfolk%2c+england&msbpn=5276&msbdy_x=1&msbdp=10&msypn__ftp=norfolk%2c+england&msypn=5276&new=1&rank=1&redir=false&geo_a=r&geo_s=us&geo_t=us&geo_v=2.0.0&o_xid=62916&o_lid=62916&o_sch=Partners",
    [Headers=[Cookie="ignore"]])),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}})
in
    #"Changed Type"

        Not sure where to find description of header parameters - I used this one on my guess.

         

        Power Query and legacy From Web are  fully different tools. Could consider that as broken functionality or as more strict requirements, but it works as it works. Can nothing to do with this, that's more question to Microsoft.