Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kmes912
Helper I
Helper I

Append 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. 

1 ACCEPTED 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.

View solution in original post

5 REPLIES 5
AnkitKukreja
Super User
Super User

Hi @kmes912 

 

You would have to restrict your data and probably reduce the data. or try using the incremental refresh.

https://www.thepoweruser.com/2020/01/19/incremental-refresh-for-files-in-a-folder-or-sharepoint-powe...

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

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. 

jbwtp
Memorable Member
Memorable 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

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors