PowerBI takes more than 3 hours to update

[awganzarolli]

Hello. I´m facing a major performance problem that is impacting the operational team. 

I´m working a  in a paginated report that imports data from many XLSX sorces. Historical data are from a big xslx that contais only text and numbers and the updates came from email. in a total of 16 files per day.

It combines those tables and treat the duplicates based in update date (a filed in all tables)  -- See the graphics, I thinks it explains better.

The problem is, besides I think its not a huge database (less than 500mb in total), it takes too long for updates and sometimes it does not update at all, forcing me to open the Power Query and treat data somehow to solve those performance issues, that I dont know how, but it starts to woks.

When I´m in power query and force update tables one-by-one, it reads all files without any problem. But when they combine files it takes long time and uses 100% of CPU, 100% of memmory and 100% of HDD usage. 

The machime I´m using is a Core i7 10th gen. 16GB of RAM, 256GB SSD.

Table relations. (fChamados aka fTickets)

I already tried to use only local files instead of emails but no sucseed at all. 

Please, help-me ! 

[StefanoGrimaldi · Accepted solution]

hey theres a lot of points to take in consideration: 

1) normally power bi desktop in volume production use its for preparing the model not runnig the whole refresh as the PC requeriments can in most of production scenarios not be enought, that what the power bi service portal its for to make the refresh process be between microsoft servers and the sources directly using gateways (algo capacity vary upon licence)

2) consider using incremental refresh just to refresh the new data added and not the whole data

3) run a performance analysis avaible on the tools tabs, also use dax studio to find possible point of possible performance issues on the dax or the folded queries. 

4) excel doesnt support folding on the source, excel isnt anywhere near being a optimal data source for volume data 

5) use tabular editor to run a best practice rules on your model that can pinpoint in little time lots of points for improvment

6) dax: there are several ways to write dax and get the same result but each way have specific performance impact upon the case its used on

7) etc 😄 

if this help give some kudos, and mark as solution if solved the problem