Forum Discussion

stever78's avatar
stever78
Brass Contributor
Aug 27, 2021

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's avatar
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's avatar
    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


    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 02, 2021

      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. 

      • msadinen's avatar
        msadinen
        Copper Contributor
        Mar 06, 2023
        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

Resources