Forum Discussion

Sbudd's avatar
Sbudd
Helper II
2 years ago

Dont refresh full table

I am using PBI desktop to creatre my dashboards, I understand the dont refresh doesnt work for Service.

I have a large MYSQL view that I currently import daily, and manipulate heavily with powerquery. This data is over 5 years of daily data, and I dont need to refresh anything over 6 months.

What options do I have to speed up refresh?  (Currently 2 hours but a lot of the time it times out)

Incremental refresh isnt an option, unfortunatley.

 

Thanks in advance.

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Sbudd I was about to say incremental refresh but seems like that is out of the picture.

     

    I will create 2 tables, one with historical data, and turn off refresh on this and the another one with the recent data (6 months) in your case and then append these together for the final output. That way you are only loading the new data not all of it.

     

    Also, when you are working SQL like source, I would recommend doing the heavy lifting at the source, creating views and trying to prepare/transform data there instead of doing it in Power Query. 

    • Sbudd's avatar
      Sbudd
      Helper II
      2 years ago

      Thanks for the reply, I originally split into 5 tables one for each year, but even though i turned off refresh, I noticed that when you append the tables it still refreshes each table. (Once published to server).

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    Duplicate the 5+ Year View to show only the last 6 months, and connect to that one.

    • Sbudd's avatar
      Sbudd
      Helper II
      2 years ago

      Thanks for the reply, but if you append the tables then it still refreshes on Server