VERY slow refresh in Desktop

[damian_bell]

Hi, 

I have built what I think is a relatively small report in PBI Desktop. It contains 5 tables most of which are <20 rows and <20columns. One table 'Portfolio' contains the main data for the report but even that only has 1076 rows. 

The data source for the Portfolio table is a Sharepoint Online list. This source has c.250 columns of which I import 11. It has 4 calculated columns and 4 measures. So tiny compared to some reports.

However when I click 'Refresh' on the ribbon in Desktop it takes over *25 minutes* to refresh the data!! I can see by the progress box that all the rows load within a few seconds but the Portfolio table states that is importing 'X.X MB from https://SharepointSite". This number stalls and I tend to cancel the refresh but even cancelling can take 7 or 8 minutes - just to cancel!
Clearly something isn't correct but I've researched and tried several commonly suggested issues (looking at performance of individual visuals etc) but it doesn't change the very slow refresh. 
Any advice? Is it the report? Or should I be looking at the Sharepoint site? 

Thanks!!

[v-diye-msft · Accepted solution]

Hi @damian_bell 

1. Please check whether there existing any step in Power Query consuming too much CPU or memory. And try to use Table.Buffer to improve the data load performance.

2. Use DAX studio/ Performance Analyzer to check out which's the most time-consuming:

Follow this blog to install the dax studio,then connect the opened pbix file in dax studio

https://docs.microsoft.com/en-us/power-bi/desktop-performance-analyzer

3. If your data source is online such as in onedrive or sharepoint online, they use automatic refresh in power bi service. This means no user configuration is necessary in order for the dataset to be refreshed on a regular basis. Data refresh settings are configured for you by Power BI. For online service providers, refresh usually occurs once-a-day. For files loaded from OneDrive, automatic refresh occurs about every hour for data that does not come from an external data source. While you can configure different schedule refresh settings and manually refresh, you probably don’t need to.

[amitchandak]

@damian_bell , refer if these can help

https://community.powerbi.com/t5/Service/Why-is-loading-refreshing-data-in-powerbi-cloud-or-same-pro...
https://www.youtube.com/watch?v=eABg872TAJU
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

[parry2k]

@damian_bell how you are connecting to sharepoint, that's what it is.

[parry2k]

@damian_bell look at these posts talks about similar issue

https://community.powerbi.com/t5/Desktop/SharePoint-Online-slow-refresh/td-p/657541

https://community.powerbi.com/t5/Desktop/Slow-refresh-of-SharePoint-list-data/td-p/134425

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡