Forum Discussion

kmes912's avatar
kmes912
Icon for Helper I rankHelper I
3 years ago
Solved

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 versio...
General Comment
  • kmes912's avatar
    kmes912
    3 years ago

    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.

jbwtp's avatar
jbwtp
Icon for Memorable Member rankMemorable Member
3 years ago

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

kmes912's avatar
kmes912
Icon for Helper I rankHelper I
3 years ago

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.