Search code examples
excelpowerquery

Power Query - DataSource.Error: An unknown error occurred when navigating to the web page

I am using power query in excel and I can not scrape a page that has a table. It keeps telling me that the first step has an error. Is there something here I should change?

= Web.Page(Web.Contents("http://cdec.water.ca.gov/dynamicapp/QueryDaily?s=DMV&end=today&span=380days"))

The error is:

DataSource.Error: An unknown error occurred when navigating to the web page. Details:

DataSourceKind=Web DataSourcePath=http://cdec.water.ca.gov/dynamicapp/QueryDaily StatusCode=-2146697208

Solution

  • Try something like this in Advanced Editor -

    let
    Source = Web.Page(Web.Contents("http://cdec.water.ca.gov/dynamicapp/QueryDaily?s=DMV&end=today&span=380days", [Timeout=#duration(0, 0, 2, 0), Headers=[#"Accept-Encoding"="gzip", #"User-Agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36", #"Accept-Language"="en-US", #"Content-Type"="text/html;charset=UTF-8"]]))
in
    Source

    You're getting errors because the website isn't allowing "Excel-like" scrappers. You need to bypass it by masquerading like a browser.

    By going to Advanced Option and filling in the referer details shown in the dropdown - enter image description here

    To get the corresponding info you need to go to inspect mode in Browser > Select Network Tab > Select the Network component

    Look for the request header -

    enter image description here