none
Control whether to dynamically refresh a previous sequence of queries or use results from Datamodel RRS feed

  • Question

  • Hi, I have a sequence of queries to map data from an input table. There are three layered queries
    1,Load and Transform
      2, Map the relevant fields
         3, Merge to produce input file for downstream system.

    Query 3 takes a long time to run (10 mins) and there is no propgress or visual evidence that it is running.
    However, if I save the output from Query 2 as a table, and reference this in Query 3, it takes seconds to run.

    My Question is:  How can I get query 3 to reference query 2 without dynamically refreshing query 2, I would like to use the static result from the last time Query 2 was refreshed.

    Many thanks.

    Wednesday, October 7, 2015 1:37 PM

Answers

  • We currently don't have a feature that allows you to cache query results across refreshes, but there are definitely scenarios where this would be helpful.

    I've passed along your feedback. We'll take it into account as we make plans for future improvements. You might also want to add this as a suggestion to UserVoice (https://support.powerbi.com/).

    In the meantime, the approach you described of loading the cache-able data to a worksheet, and referencing it from there in your other queries, is probably your best option (unless the data is too large to be loaded in its entirety to a worksheet).

    Ehren


    Wednesday, October 21, 2015 7:17 PM
    Owner

All replies

  • Do you see any perf improvement if you wrap Table.Buffer calls around the last steps of the queries in step 2?

    See this question for more info:

    https://social.technet.microsoft.com/Forums/en-US/12c51d1e-c236-4ff6-9c49-3c9b70a59f25/use-of-tablebuffer-in-references?forum=powerquery

    Ehren

    Friday, October 9, 2015 8:35 PM
    Owner
  • Hi

    Buffering did not seem to make a difference in this case.  I am working with csv data so there is no server side processing.  Form what I observe sometimes refreshing the final query forces the upstream queries to refresh (slow), sometimes it doesn't (fast).  What I have noticed is that if I refresh each query in sequence (with table load) the end to end time is shorter and more consistent.

    What would be good is if it was possible to prevent the downstream queries calling a refresh of the upstream queries, is there a way to force PQ to treat the upstream queries as static tables.

    At times if looks as if PQ does this automatically, other times not so sure.  

    Many thanks.

    Tuesday, October 20, 2015 5:52 PM
  • We currently don't have a feature that allows you to cache query results across refreshes, but there are definitely scenarios where this would be helpful.

    I've passed along your feedback. We'll take it into account as we make plans for future improvements. You might also want to add this as a suggestion to UserVoice (https://support.powerbi.com/).

    In the meantime, the approach you described of loading the cache-able data to a worksheet, and referencing it from there in your other queries, is probably your best option (unless the data is too large to be loaded in its entirety to a worksheet).

    Ehren


    Wednesday, October 21, 2015 7:17 PM
    Owner
  • Thanks Ehren, will do
    Wednesday, October 21, 2015 9:59 PM