Power Query slow refresh and TABLE.BUFFER

[stever78]

Hi ,

very much appreciate your help with this

i 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,  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).

so how should this code read if i inserted the table.buffer?

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.

thanks

steve reeves

[#2]

@stever78 

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.

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.

Aggregations like Table.NestedJoin could really affect performance. Depends on how your queries are designed Table.Join instead could improve the performance.

I'd recommend to check series of Chris Webb posts related to Power Query performance, one of them is Chris Webb's BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 3: Table.Jo... and here you may find links on other post from this series. 

[#3]

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

[#4]

@stever78 

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. 

[#5]

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

[#6]

@stever78 

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.

[#7]

@stever78
Did you find a solution to the slow refresh issue?
I also have a big-time problem with my live data loading & refreshing speed... I've searched everywhere and found nothing...

[#8]

@John_Proistakis 

IMHO, there is no universal solution. Depends on many things. What is your data source, do you use query folding, did you separate staging queries, etc, etc, etc.

[#9]

Hi Sergei Baklan,

I have a query where I am doing 10 joins, Could you please help me how to use table .Buffer and NestedJoin in that case - Please help? Thanks

[#10]

@msadinen 

Sorry, I didn't catch what is you problem exactly. To make joining more reliable you may use Table.AddKeys() to declare primary keys for each of joined table.