Forum Discussion
Helper IAppend Tables creating timeout issue on Power BI service
I have a Power BI that pulls from ~300 different Sharepoint online lists, appends them into 3 tables and reports out metrics based on inputs into the lists. It refreshes locally on the desktop version (takes a long time) but when I publish up to the service, at 2 hour mark it gives the below timeout message "Processing error:Timeout expired. The timeout period elapsed prior to completion of the operation."
I have seen guidance how to override the SQL timeout in Power Query but have yet to see how to do it in this use case. I've tried breaking the append tables from 3 (groups of ~80 lists into 3) into 6 (groups of ~50 into 6) but didn't get the refresh under 2 hours. If this idea does reduce run time (breaking into less large groups), I could go this route but not sure if it's even worth doing.
Have also considered using summarize or another function to append the tables instead of using append in Power Query but again not sure if it's even worth taking the time or would matter.
I ended up deleting the appended combination tables, made a virtual table using UNION of all 300 tables. Seems to be doing the same thing and is having the refresh time <2 hours.
5 Replies
- AnkitKukreja
Super User
Hi kmes912
You would have to restrict your data and probably reduce the data. or try using the incremental refresh.
kmes912Hi - the data set ends up being only ~20K rows, so I don't think reducing it is going to help. I could likely pull the groups down further into smaller batches but I'm not sure if that even helps this error message and would get it under 2 hours.
jbwtpMemorable Member
Hi kmes912,
If refreshing dataset fetching 20k rows takes over 2 hrs, I would suggest that the code that does it maybe suboptimal. Considering, this involves a high number of SP lists, they may be loaded and processed multiple times (I saw examples where it was happening thousands of times due to code structure).
Based on the output size, I would suggest that the input tables are not that big too. This, kind of, suggests using Table.Buffer to increase processing speed - tipically at a point imediatelly preceeding mergers (this especially relevant for the joining [typically right-side] tables). If you can share your code, I can potentially suggest where you can look at.
Cheers,
John
