Forum Discussion
Power Query slow refresh and TABLE.BUFFER
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.Join And SortMerge Chris Webb's BI Blog (crossjoin.co.uk) and here you may find links on other post from this series.
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
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.
- 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? ThanksSorry, 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.
- 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 steveWith "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.
