none
After loading to data model, refreshment becomes slower RRS feed

  • Question

  • Hi,

    I have a list of queries, e.g., Q1,Q2...Q8. Q6 and Q7 are created using Q1...Q5. Then I loaded Q6 and Q7 to Excel worksheet. Using those two worksheets, I created Q8.

    At the beginning Q8 takes just around 10s to refresh (because Q8 uses Excel tables instead of queries as data source, if I use Q6 Q7 as the data source directly, it will take longer to refresh, more than 1 min). However, after loading the Q8 to Data Model, it takes more than 2 mins to refresh.

    Can anyone explain why is it?

    Thanks

    Wednesday, October 28, 2015 4:26 PM

Answers

All replies

  • Hello

    I think your question partly described here:

    https://social.technet.microsoft.com/Forums/en-US/7e3870a3-2161-4d11-9721-a317e2634034/power-query-executes-the-query-twice


    Maxim Zelensky Excel Inside

    Thursday, October 29, 2015 12:16 PM
  • Hi,

    I may find out one solution to this. Do not load the query to the data model directly. Load it to the excel worksheet first and then load the excel table to the data model. 

    Yesterday, I tested it for several times. If I loaded the query to data model directly and used the power pivot to generate pivot table, refreshing the pivot table took more than 2 mins; if I loaded the query to the excel table, loaded the table to the data model, and used power pivot to generate pivot table, refreshing the query took less than 30s and refreshing the pivot table takes 2s, totaling about 30s.

    Thursday, October 29, 2015 3:40 PM
  • Would you be able to send a frown so we can take a closer look?
    Friday, October 30, 2015 11:12 PM