Why does Power Query re-load my data source on simple query steps on small tables?

[aseagull]

Hi. I am sure someone can point me to a good resource on this.

I have a simple table with a MERE 52 rows. I just want to add a column concatenating two others, and change the type. It takes several minutes.

Looking at the diagnostics, each step is spending over 90 seconds re-GETing data from my Sharepoint site. (These queries all source from CSV files in Sharepoint, though this particular one is computed from other queries.)

Why on earth does it need to re-GET the data for these 52 rows, much less *twice*?? Is there a setting I'm missing?

Thanks,
Amon

[aseagull]

Nate, I had some time to re-run with diagnostics, after switching to Sharepoint.Contents() and confirmed, unfortunately, that my query is still re-GETing from Sharepoint. This is a small query, many queries removed in the stack from the one seeing the slowdown.

It doesn't help that Sharepoint is taking 8-9 minutes sometimes to return a file. I suppose that's an unrelated problem.

My data are small, so that's not the issue.

Any other tips?

[aseagull]

For anyone who finds this thread, my particular problem was resolved with Table.Buffer(), as found here:
https://community.powerbi.com/t5/Desktop/Why-does-Power-BI-quot-Load-quot-much-more-data-volume-than...

Thanks to everyone for all the input!

[V-pazhen-msft]

@aseagull 

If this "simple" table is calculated based on other queries from multiple large CSV files, it could take sometime, because they need to reload all the others queries then process the query steps to get the refreshed 52 rows table. 

Try check the background data in the options see if helps.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[aseagull · Accepted solution]

Thank you Nate and Paul. I believe Sharepoint.Contents() has helped the situation, but as it's still slow, I need to do some more debugging. (I do have the background fetch option checked.) It will probably be a few weeks before I can dig back into it. Thank you both!

[watkinnc]

If you are using SharePoint.Files, it's probably because the connector reads all of the files in SharePoint before running your actual query. Try using SharePoint.Contents instead.

--Nate