none
Separate Queries Refresh with Append? RRS feed

  • Question

  • Hello All

    Say we have a data model built from 3 different sources:

    1) Historical data from a big Access file (7+ million rows, set to never refresh in the data connections)
    2) Daily updates csv folder (8k rows daily files, set to combine & refresh on file open)
    3) Current csv file (an 8k rows file, set to refresh every 30 mins)

    We built a query from all of them appending them all. But there is a refresh problem: if we need the last 30 mins included in all the data, the query is currently refreshing all of them, making it super slow/inefficient.

    What we´re looking is to have all historical data till now (with no more than 30 mins delay), but without the need to refresh all the data every 30 mins... just the needed part, which is the 3rd: the current csv file with 8k rows only.

    Is this possible? and how?

    Thanks so much for your support. You´re doing an outstanding work. :)

    Saturday, June 28, 2014 6:24 PM

Answers

  • Hi Nico,

    By appending these three queries into one, all refresh operations on the final query will result in upstream dependencies being refreshed as well. There is no way to work around that.

    If the tables were of a relatively small size, one "hacky" way of controlling this behavior would be to load the three queries into the worksheet, and then using Power Query's "From Table" to connect to the three output tables and append these tables instead of the original queries. That way, you'd pick the latest data available on the sheet, which would be refreshed according to the rules that you defined above.

    Given that your source #1 is 7+ million rows, this won't fit in the worksheet so this suggestion won't work for you. I think you should look into ways of decoupling this table from the other two, and have them loaded separately into the Data Model if possible. Alternatively, maybe having this database file sitting locally would improve the load time?

    Hope this helps.

    Thanks,
    M.

    Tuesday, July 1, 2014 5:23 PM

All replies

  • Hi Nico,

    By appending these three queries into one, all refresh operations on the final query will result in upstream dependencies being refreshed as well. There is no way to work around that.

    If the tables were of a relatively small size, one "hacky" way of controlling this behavior would be to load the three queries into the worksheet, and then using Power Query's "From Table" to connect to the three output tables and append these tables instead of the original queries. That way, you'd pick the latest data available on the sheet, which would be refreshed according to the rules that you defined above.

    Given that your source #1 is 7+ million rows, this won't fit in the worksheet so this suggestion won't work for you. I think you should look into ways of decoupling this table from the other two, and have them loaded separately into the Data Model if possible. Alternatively, maybe having this database file sitting locally would improve the load time?

    Hope this helps.

    Thanks,
    M.

    Tuesday, July 1, 2014 5:23 PM
  • Thanks Miguel, I reduced the amount and is working now.

    Sorry for the late reply.

    Wednesday, July 23, 2014 9:37 PM