none
Excel Query: Fail to load table from query editor to excel sheet RRS feed

  • Question

  • Unable to load data from query editor to excel sheet

    Below M code for reference, this code  works perfectly in query editor. Data are completely fetch from the site.

    let
        Source = Web.Page(Web.Contents("http://edge.pse.com.ph/disclosureData/dividends_and_rights_info_form.do")),
        Data = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data,{{"Company Name", type text}, {"Type of Security", type text}, {"Type of Dividend", type text}, {"Dividend Rate", type text}, {"Ex-Dividend Date", type date}, {"Record Date", type date}, {"Payment Date", type date}, {"Circular Number", type text}})
    in
        #"Changed Type"

    However, when I load the data to excel sheet, error occurs.

    I encountered multiple errors such as timeout within 100 seconds, company name table reference, unknown error when navigating to web page, etc...

    I've been trying and researching to resolve this problem for weeks with no success till date...

    I didnt face this problem with other sites. 

    Appreciate some advice/solution


    Tuesday, April 30, 2019 11:04 PM

Answers

  • Hi Lukas,

    It looks like that page is loading its content dynamically. Sometimes we happen to grab the page content when the stock info has been loaded, but most of the time we seem to be grabbing it before the stock info is present. In addition, it looks like the page may be doing some throttling of the requests, resulting in the timeouts you're seeing.

    I would recommend seeing if you can find this info on another page, or trying the new web table inference preview feature in Power BI Desktop. The Web.BrowserContents function can be passed an option to wait before grabbing the page content, which makes it possible to handle these kinds of dynamic pages.

    Ehren

    Monday, May 6, 2019 6:49 PM
    Owner

All replies

  • Hi Lukas,

    To help you better resolve the issue, I will move the thread to Power Query Forum. You may get more helpful replies there.

    Thanks for your kind understanding.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, May 1, 2019 8:08 AM
  • Thank you
    Wednesday, May 1, 2019 8:51 AM
  • Hi Lukas,

    It looks like that page is loading its content dynamically. Sometimes we happen to grab the page content when the stock info has been loaded, but most of the time we seem to be grabbing it before the stock info is present. In addition, it looks like the page may be doing some throttling of the requests, resulting in the timeouts you're seeing.

    I would recommend seeing if you can find this info on another page, or trying the new web table inference preview feature in Power BI Desktop. The Web.BrowserContents function can be passed an option to wait before grabbing the page content, which makes it possible to handle these kinds of dynamic pages.

    Ehren

    Monday, May 6, 2019 6:49 PM
    Owner
  • Hi Ehren

    Thank you for  your response.

    Web.BrowserContents works in PowerBI desktop. However, this function does not work in excel. Is there an alternative for excel? I use Excel mainly because of its AutoRefresh capability (which is not available in PowerBI Desktop in my knowledge). 

    Regards

    Lukas

    Tuesday, May 21, 2019 4:34 PM
  • Hi Lukas,

    Web.BrowserContents is not currently available in Excel. Power BI does have the ability to auto-refresh if you publish your report to powerbi.com. If your report accesses local sources (i.e. sources not accessible from the cloud), then you'll also need to install and configure a gateway.

    Ehren

    Tuesday, May 21, 2019 4:37 PM
    Owner