List.Buffer – make Power Query much faster

The List.Buffer function can speed up Power Query evaluation. It ensures that the content of the query is loaded once, and then it is just queried by following query steps. So it doesn’t have to be loaded again and again.

Example:

This syntax assigns each row the Revenue value from the previous row:

• let
     my_step = start,
     diff = Table.AddColumn(my_step, “Difference”, each my_step[Revenue]{[Index]-1}-[Revenue])
  in
     diff

However, loading takes very long time – e.g. 1 minute for a table with around 1500 rows.

But if you first buffer the list into memory and then refer to the loaded list, it is shortened to approx. one second, so the loading time is incomparably shorter.

• let
     my_source = start,
     buffered = List.Buffer(my_source[Revenue]),
     diff = Table.AddColumn(my_source, “Difference”, each buffered{[Index]-1}-[Revenue])
  in
     diff