Excessive time to refresh sharepoint tables with BI Desktop

[ec0014]

Good morning,
I'm wondering about my use of Power BI because it takes me several hours to successfully update my data... let me explain!
I create my projects via Power BI Desktop to monitor my team's activity (field interventions). All of our activity is managed via sharepoint. So I use sharepoint as a data source. As a general rule, I have at least 3 data tables (client / interventions / prices) of the sharepoint list or library type filtered on folder + a date table.
via power query I generally delete a lot of data but I still have a nice data table. When updating I sometimes take several hours to do an update. 1 time out of 2 I find myself canceling... in short, it's not functional.
I tested using the web service directly from sharepoint... it seems simpler but the problem is that I don't understand how data tables are linked to projects created via sharepoint...
In short, I would need a little help to understand the user cases of the Web service vs. the Desktop service and especially if I can improve my updating process.

[christinepayton · Accepted solution]

The refresh speed of SharePoint queries depends on a lot of things - one of the best ways to speed things up is to use the 2.0 SPO List connector instead of 1.0 - 1.0 is the default. It's hard to change existing queries to use this, though, because the column names come through completely differently.

The other thing you want to do is to use foldable query steps in Power Query, and remove any columns you're not using. Try to avoid things like adding an index column, which will 100% slow things down a ton. If you have a lot of non-foldable transforms that'll cause problems.

If your queries are relatively simple and use the newer connectors, it can handle hundreds of thousands of rows without much trouble, but you have to be intentional about setting it up sustainably if you have a lot of data. 

[v-zhengdxu-msft · Accepted solution]

Hi @ec0014 

Are you doing a lot of transforms in Power Query? It should not take several hours.

Maybe you can try to use the dataflow, that can take it faster.
Here is the features supported by Dataflow Gen2:

Pattern to incrementally amass data with Dataflow Gen2. Accumulating data incrementally in a data target requires a technique of loading only new or updated data into the data target. This technique can be done by using queries to filter data based on data targets.

Here are some links for your reference:

Pattern to incrementally amass data with Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

Differences between Dataflow Gen1 and Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

Or you can try Azure Databricks SQL Warehouse or Azure Data Lake:

What is a SQL warehouse? - Azure Databricks | Microsoft Learn

Power Up your BI with Microsoft Power BI and Lakehouse in Azure Databricks: part 2 - Tuning Power BI...

Pros and Cons of Azure Data Lake Storage 2024 (trustradius.com)

Data lakes - Azure Architecture Center | Microsoft Learn

Why Should I Use Azure Data Lake? (nextpathway.com)

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ec0014 · Accepted solution]

Thank you very much for this information. I didn't understand the difference caused by the connector. I think I have actually favored SPO 1. I will take your advice into account and see if it works better.

[v-zhengdxu-msft · Accepted solution]

Hi @ec0014 

Are you doing a lot of transforms in Power Query? It should not take several hours.

Maybe you can try to use the dataflow, that can take it faster.
Here is the features supported by Dataflow Gen2:

Pattern to incrementally amass data with Dataflow Gen2. Accumulating data incrementally in a data target requires a technique of loading only new or updated data into the data target. This technique can be done by using queries to filter data based on data targets.

Here are some links for your reference:

Pattern to incrementally amass data with Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

Differences between Dataflow Gen1 and Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

Or you can try Azure Databricks SQL Warehouse or Azure Data Lake:

What is a SQL warehouse? - Azure Databricks | Microsoft Learn

Power Up your BI with Microsoft Power BI and Lakehouse in Azure Databricks: part 2 - Tuning Power BI...

Pros and Cons of Azure Data Lake Storage 2024 (trustradius.com)

Data lakes - Azure Architecture Center | Microsoft Learn

Why Should I Use Azure Data Lake? (nextpathway.com)

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[christinepayton · Accepted solution]

The refresh speed of SharePoint queries depends on a lot of things - one of the best ways to speed things up is to use the 2.0 SPO List connector instead of 1.0 - 1.0 is the default. It's hard to change existing queries to use this, though, because the column names come through completely differently.

The other thing you want to do is to use foldable query steps in Power Query, and remove any columns you're not using. Try to avoid things like adding an index column, which will 100% slow things down a ton. If you have a lot of non-foldable transforms that'll cause problems.

If your queries are relatively simple and use the newer connectors, it can handle hundreds of thousands of rows without much trouble, but you have to be intentional about setting it up sustainably if you have a lot of data.