cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aseagull
Helper II
Helper II

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

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

1 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!

View solution in original post

5 REPLIES 5
aseagull
Helper II
Helper II

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?

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
Community Support
Community Support

@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.

 

V-pazhen-msft_0-1622180503431.png

 


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

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!

View solution in original post

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors