Power Query SharePoint Connections Refresh Speeds

[Jack95]

Hi,

I have a number of queries connected to SharePoint - the queries are purely to extract metadata on the Site itself - permissions, record count, date fields, etc. - I'm not trying to extract data from specific files on the Site. The Site is quite large and there are >400 users and numerous folders and individual libraries, so the queries are PAINFULLY slow to refresh.

I appreciate that there's a lot of data involved and a number of queries also with many different steps - some of the queries also involve connecting to my organisation's AD to extract data on User Groups - but I can't help but think that there must be ways of speeding up the refresh process. Does anyone have any ideas or suggestions on how to make the queries run more efficiently?

Any help is much appreciated.

Many Thanks,

Jack

[v-kathullac]

Hi @Jack95,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MasonMA for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.

Thanks

[PwerQueryKees]

I am using the sharepoint.file quite a bit on a 10K file + sharepoint site.

Yes, it takes some time and filtering early does not seem to make any difference.

You can try your luck with Sharepoint.Contents(). It is much faster, but does not return thhe same data and returns it in a different format. I also expereinced som unexplicable errors at some point, but that is 2 years ago and may be resolved now...

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[Supercat]

Let me clearify my problem now. And I use the function SharePoint.Contents in the following file.

After June, the same file in the Desktop PQ refreshes very slow(may not success) and the memory goes up very high then the program doesn't respond. At the same time, I open another file(the old version) works. So I think it's not the problem of the network.

I turn to suspect maybe this file goes error.  But another day this file can work in the PQ refreshing. So I am not sure what happens. The file today refreshes successfully, another day fails. What goes wrong after all? I don't know. 

[Supercat]

I am not very sure if we have met the same problem. 

Actually, after June's update, I find power query in Desktop connceted to Sharepoint goes very slow. I even don't know how to get help. When I refresh some tables from Sharepoint, the Memory of the PC will goes very high after a long time and still the data is not refreshed.

I can't help think what Microsoft Fabric Team have done in the connection because I remember in 2023 the function “SharePoint.Contents" went error,too.

Please check this, thank you

[v-achippa]

Hi @Jack95,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MasonMA for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Jack95,

We wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[MasonMA · Accepted solution]

@Jack95 

Hi, the best approach would be use filtering in the Source step or as early as possible to avoid loading full content of lists if you're only interested in metadata like item count, permissions, or last modified date.

There's official Microsoft documentation recommending filtering as early as possible in Power Query. This means placing your Table.SelectRows(...) filters immediately after the data source step so that fewer rows are pulled into Power BI, reducing memory and processing time.

Best practices when working with Power Query - Power Query | Microsoft Learn