Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
Hi @kmes912
You would have to restrict your data and probably reduce the data. or try using the incremental refresh.
Hi - 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.
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
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.
HI - do you mean like the code on the advanced editor? None of them have anything complex in them that I can see that would reason it taking so long.
-----------------------------------------
The individual 300 tables just have this basic transformation, load and do one change
let
Source = SharePoint.Tables("https://abcdefg.sharepoint.com/sites/abcdefg/", [Implementation=null, ApiVersion=15]),
#"36dc1415-c47f-4be2-ae4e-a48b3db90352" = Source{[Id="36dc1415-c47f-4be2-ae4e-a48b3db90352"]}[Items],
#"Expanded ParentList" = Table.ExpandRecordColumn(#"36dc1415-c47f-4be2-ae4e-a48b3db90352", "ParentList", {"ParentWebUrl"}, {"ParentWebUrl"})
in
#"Expanded ParentList"
---------------------------
The combination tables, where I had 50 of them combine to 6 did this
let
Source = Table.Combine({#"abcde", #"bcdef", #"cdefg"}),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Title", "Dept"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Modified", type date}, {"Created", type date}, {"WeekEndingDate", type date}, {"FinanceComments", type text}, {"DepartmentComments", type text}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [Dept] <> "0001")
in
#"Filtered Rows2"