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?

 

 

[skythe83]

A bit late to the party, but this page is in the top google results, so may it help others:

While working in the PQ Editor, if the steps are loading too slow, I go to one of the earliest steps and limit the number of rows.
If you pull data from only one file, insert a step right after the second step ("navigation") -> Keep Top Rows -> 5 or 100 depending on how much data variation you need.

If you pull data from multiple files or a folder, force a filter to only give you one or two files and also there limit to the top 5 rows in the "Transform Sample File" Query.

When I'm done with all the changes, I remove these steps again.

Another thing to do, if you do PQ in Excel: do not output your query directly in to Excel, instead, create a reference query and output this reference. This way, when you change something in your steps, the query does not force-refresh into Excel every time.
When you start from scratch, for example if you would normally name your query "Sales", name it "Sales_Processed" instead, then right click and reference it into a new query, name that query "Sales". Make sure to add all your changes into Sales_processed, not into Sales.

If you want to update your existing query "Sales" with this logic, duplicate the query, rename the new duplicate into Sales_Processed, go to "Sales", delete all steps except source (you can right click and delete the query after Source to have an option to delete everything below). Then change the Source to "=Sales_Processed". 
Now your Excel will stop constantly refreshing.

[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