Sharepoint & PowerBI reporting refresh issues

[RalZRR]

Hello,

I'd like to hear some experience as to how to deal with a certain scenario. I refuse to believe I am the first one to encounter this.

We use PowerApps to get hours worked from employees, which then goes in to a Sharepoint List.
There is additional SP list with employees and details, such as rates, teams etc. (O365 account is used as identifier)

We are creating a BI report where the data is displayed in a matrix as well as calculations are made where the hours are spent and how much they have cost.

The BI report uses a Dataflow, which queries both lists (hour list and employee details list)

The hour reporting list currently has less then 2K entries, but the Dataflow refresh for that table takes more than 25 minutes, as well as working with the dataflow becomes basically impossible because transforming the data (some steps, and this would be a one time deal, but still) results in Sharepoint timing out the connection - Too many requests.

I did find the "solutions" suggesting to use Sharepoint REST API to collect data, but it still will hit the hard ceiling of 5K entries per API Call.


So here's the question: How come that the SP list, by itself, has an Export to Excel feature, which works, basically, immediately, but there is no option to leverege this feature through any means?

Microsoft Flow "Get Items" step also has a hard limit of 5K entries so that's out of the question as well.

"Get an SQL database" - yeah.

Are there solutions to this problem or do the products really not work with themselves together that good?

[RalZRR]

Indeed running the report with a dataset instead of dataflow provides, basically, an instant data refresh, which now takes 1 minute.
I'd guess that there is something to look in to as to why dataflows take so long to refresh when compared to datasets when consuming the same ammount of data from the same source.

I wouldn't say that this is a solution-solution, but it works for now for our scenario.

Thank you Aniya Zhang!

[v-yueyunzh-msft · Accepted solution]

Hi, @RalZRR 

According to your description, your data flow is connected to the Sharepoint data source, but the data volume refresh lasts for a long time, and finally reports an error: Sharepoint connection timed out - too many requests. For the problem of data stream refresh timeout, the suggestions we can give can be divided into two aspects:

1. You can try to optimize the complexity of the ETL process of the data flow and reduce the two-table Merge and Append operations in the data transformation step in the data flow.
2. You can try to refresh the amount of data to avoid the timeout caused by too much data that needs to be refreshed. You can try to use the "Filter" function in Power Query online to try to filter the date column to reduce the data to half of the original Or a quarter, try to see if the refresh will take effect.

Another point we would like to confirm with you is whether you can try to consider the possibility of using datasets instead of data streams, because datasets can be refreshed through XMLA endpoints by connecting to SSMS, and this refresh has no time limit, You can do this by creating a blank query in the desktop to copy-paste the M code from the "Advanced editor" into it and give the same credentials For specific documents, please refer to this: Dataset connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft Docs

At the same time, regarding the difference between the export data from the SharePoint API and the "Export to Excel" function of the SP list itself that you mentioned later, I'm sorry, you can try to open a post in the SharePoint forum to inquire about SharePoint experts, they will know more and give professional answers.

Best Regards,

Aniya Zhang

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