none
Can I cache Web contents from Web by Power Query ? RRS feed

  • Question

  • Can I cache Web contents for Web Requests from Power Query ?
    I want to build Power Query from Web more smoothly.

    When I build query by requesting over 100 web,
    I build query by a lot of waits, and check results, and return to build, ...
    At this time, 100 web result is same between build, build, build...

    So, If Power Query can cache web request result,
    I may be create more shorter time, more smoothly.

    Regards,
    Yoshihiro Kawabata

    Friday, May 30, 2014 2:47 PM

Answers

  • Hi Yoshihiro,

    Power Query uses a local cache for the Query Editor preview data. However, it will always retrieve the latest data from the data source when loading a query to the worksheet/Data model.

    Something that might work for you (yet not an ideal or recommended path in general) is having different queries for retrieving the data from the web, which you don't want to refresh always, and having a different query that references the output of the previous one and applies the subsequent steps that you want to refresh more often. You could load the output of the first query to the worksheet, and reference the worksheet table in the 2nd query (i.e. using "From Excel table"). This will give you the ability to only refresh the first query when you want, while letting you interact with the steps in the second query and refresh several times.

    Thanks,
    M.

    Saturday, May 31, 2014 5:25 PM

All replies

  • Hi Yoshihiro,

    Power Query uses a local cache for the Query Editor preview data. However, it will always retrieve the latest data from the data source when loading a query to the worksheet/Data model.

    Something that might work for you (yet not an ideal or recommended path in general) is having different queries for retrieving the data from the web, which you don't want to refresh always, and having a different query that references the output of the previous one and applies the subsequent steps that you want to refresh more often. You could load the output of the first query to the worksheet, and reference the worksheet table in the 2nd query (i.e. using "From Excel table"). This will give you the ability to only refresh the first query when you want, while letting you interact with the steps in the second query and refresh several times.

    Thanks,
    M.

    Saturday, May 31, 2014 5:25 PM
  • Thank you, Miguel

    My Senario like Devin Knight's roller coasters Excel.
    http://blogs.msdn.com/b/powerbi/archive/2014/05/28/explore-the-worlds-roller-coasters-with-power-bi.aspx

    This Excel get Roller Coaster Data from rcdb.com by Excel Power Query.
    rcdb.com have 280 pages on roller coaster search web page.

    Step 1: build function Power Query for get one result page from rcdb search page.
    Step 2: build Power Query for get all result page from function Power Query by 280 times.
     = {1..280}
     It take over 10 minites.
    Step 3: Debug function Power Query for clear error and for build Power Pivot/Power View.
    Step 4: refresh Step 2 Power Query , over 10 miniues.

    IF Power Query cache all web page results, seems to under 1 minues for Step 4.

    Regards,
    Yoshihiro Kawabata

    Sunday, June 1, 2014 3:00 AM