In my previous post I wrote about one interesting technique used by my colleague Zoltán Kaszaki-Krsjak.

It is a trick that you most probably will not use in any of your solutions. However, worth to know how it works and how to do this without adding new columns.

“each Buffer[Category]{[ID2]}?” is just a function, so we can combine it with methods described in one of my posts (Transform Column Using Custom Function).

Here is the code.

File with methods is here.

But which method is faster?

Let’s make a series of tests with fake data.

I used sample table with 5000, 10000 and 20000 rows, with

  • buffering (Table.Buffer) and without.
  • With clearing of cache and without.

I cleared cache – no impact

Code that I used for tests

What makes sense is order of refreshing.

1-2-3, 1-3-2, 2-3-1 and so on – show different timing

Here are the results

Test conditions: nothing special, just daily usage of laptop in parallel while waiting.

All in all, such “shift of cells” is a very slow operation, I wouldn’t recommend to use is for massive tables.

Used methods are very close in time of execution, maybe in some conditions results can be different.

You may download workbook with queries and macro if you want to check described methods by yourself.

3 thoughts on “Shift cells up/down in same column in Power Query”

  1. Hi Ivan!
    Can you try the next one:

    = Table.Join(Buffer[[Index]], “Index”, Table.AddIndexColumn(Buffer[[Category]], “Index2”,0,1), “Index2”)[[Index],[Category]]

    Is it slower?

    1. @Maxim

      amazing speed, somehow M seems to be SQL under the hood …

      Query – Method1 10000 34,89453
      Query – Method2 10000 34,96875
      Query – Method3 10000 34,99219

      Query – Method4 10000 0,03125

    2. Thanks, Maxim! This one is much better, for sure. Just added a “JoinKind” argument

      Table.Join(Buffer[[Index]], “Index”,
      Table.AddIndexColumn(Buffer[[Category]], “Index2”,0,1),
      “Index2”, JoinKind.LeftOuter )[[Index],[Category]]

Leave a Reply