Power Query slow refresh and TABLE.BUFFER

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

BI & Data Analysis

excel

SergeiBaklan

Diamond Contributor

Aug 27, 2021

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.Join And SortMerge Chris Webb's BI Blog (crossjoin.co.uk) and here you may find links on other post from this series.

stever78

Brass Contributor

Sep 02, 2021

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

Forum Discussion

Power Query slow refresh and TABLE.BUFFER

Resources