Data Refresh is too much slow in Power Query

[aAmirkHan]

Hi,

There are two excel files (INCData and SRData) which are placed on SharePoint. One file size is 336 MB and other file size is 228 MB. I am fetching data from SharePoint to Power Query by using SharePoint Folder. 

However, whenever I try to change data type for the example decimal to number or Date/Time to Date then it takes a lot of time to update field or refresh data in Power Query. Moreover, I click on INCData file or SRData file then it takes a lot of time to get data refresh or 

Moreover, I do not have installed MS Excel in my office laptop due to Organization policy, so I am fetching data from SharePoint to Power Query. 

Can you please suggest me on this? How can I fix it?

 

 

[ray_aramburo · Accepted solution]

In my experience, the Sharepoint connectors are one of the slowest ones. First, the data sources have some considerable size (200-400 MB). Due the nature of the source, Power Query does not perform query folding (detailed explanation of what this is here). For that reason, Power Query will always perform each and every step of the query once you add a new step or modify one. The speed of this will depend on the processing capacity of your laptop/PC and available RAM, since it will need to work thoroughly to download that data, import it and process it. I would like to say there is a faster way to do it, but unless you use Power Query through a dataflow in a premium capacity or have a super powerful computer, unfortunately it will always take its time.

What can you do about it? Remove rows (filter) and columns that you don't need on your first step.

[aAmirkHan]

Finally, I got the good suggestion.

Thank you