Power Query slow refresh and TABLE.BUFFER

Question

Hi ,very much appreciate your help with thisi have a workbook with 50 power queries, of which 30 need to refresh ALL, at the same time.some of them time out (i get the popular "this query doesn't load with 100 secs", but not always, which is strange?i have been informed that if i DESELECT "allowing data preview in the background" then this could help.could anyone inform me why this is?, and if its a good idea. - i don't need to work on the file whilst its refreshing.the next point to improve speeds i have read,&nbsp; was to add Table.Buffer to my source code?but where do i put this ?my code to my query (where all others are pointing to is)= Table.NestedJoin(Class, {"Column2"}, myid, {"Trnid"}, "myid", JoinKind.LeftOuter).&nbsp;so how should this code read if i inserted the table.buffer?&nbsp;thanks for your help on this, its much appreciated as its taken so much time to get all the PQ's working and is just so frustrating that now it doesn't refresh properly on a consistent basis.&nbsp;thanks&nbsp;steve reeves&nbsp;&nbsp;&nbsp;

sergeibaklan · Answer

stever78&nbsp;
I don't think disabling "allowing data preview in the background" helps a lot with performance, it affects caches update in preview mode. Side effect, in preview mode you may have "evaluation was cancelled" yellow alert. Not critical, you may click on such query to refresh it. But again, that's mainly preview mode.
&nbsp;
Table.Buffer() could improve performance a bit, could not. It fixes the table in memory and useful when you sort or remove duplicates in table.
&nbsp;
Aggregations like Table.NestedJoin could really affect performance. Depends on how your queries are designed Table.Join instead&nbsp;could improve the performance.
&nbsp;
I'd recommend to check series of Chris Webb posts related to Power Query performance, one of them is&nbsp;Chris Webb's BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 3: Table.Join And SortMerge Chris Webb's BI Blog (crossjoin.co.uk)&nbsp;and here you may find links on other post from this series.&nbsp;

stever78 · Answer

thanks for the reply sergei, i wil look into the table.join function.

concerning table.buffer though, do i insert that code at the start of my join query (above), so
= Table.NestedJoin.Buffer(Class, {"Column2"}, myid, {"Trnid"}, "myid", JoinKind.LeftOuter).
instead of
= Table.NestedJoin(Class, {"Column2"}, myid, {"Trnid"}, "myid", JoinKind.LeftOuter).

how would i enter the code? Or does it not work for nested.join queries.

thanks for your help, best, steve

sergeibaklan · Answer

stever78&nbsp;
That could be like
step = Table.NestedJoin(
   Table.Buffer( Class ), {"Column2"},
   Table.Buffer( myid ), {"Trnid"},
   "myid", JoinKind.LeftOuter)
or
a = Table.Buffer( Class ),
b = Table.Buffer( myid ),
step = Table.NestedJoin(
   a, {"Column2"},
   b, {"Trnid"},
   "myid", JoinKind.LeftOuter)
Table.Buffer() do nothing but load the table into the memory.&nbsp;

stever78 · Answer

thanks for this sergei, ill try it and hope it works.if i could ask one further question of you,what does the FAST DATA LOAD do on power query settings. i have always turned this ON. As i seem to get the TIMEOUT errors when loading PQ'S.i can obviously try it without but do you know what the point of it is or is it literally that it just loads PQueries's quicker., thanks steve

sergeibaklan · Answer

stever78&nbsp;
With "Fast Data Load" Excel/Power Query allocates maximum of available resources (CPU, memory) to query downloading. During that other processes could be unresponsive. Yes, it accelerates data loading but decelerates if not stops for a while other processes.

Forum Discussion

Power Query slow refresh and TABLE.BUFFER

9 Replies

Resources