This tutorial describes, how to get value from previous row in Power Query (or from any other row…).

There are two approaches here. The first one is shorter and uses the List.Range function. The other is longer, but you don´t have to type in M directly.

Value from previous row, using List.Range

We need the animal from previous row. For example in second row there should be Hamster, which is now in the first one.

There must be an index column in table.

The syntax is like this:

  • =List.Range(
       Source[Animal],
       [Index]-1,
       1)

Because:

  • =List.Range(
       Source[Animal], – column with values we need
       [Index]-1, – first item we need is the previous one, so it is the value from Index minus 1 
       1) – we only need one item

After extraction it looks like this. The error in first row can be simply replaced by anything.

Value from previous row, without writing of M code

If you have a table like this:

and need values from previous row, like this:

How to do it?

First you need the index column:

Then add 1 (if you want to move one row back):

Then merge the table with itself. The key are the original and moved index.

Click on the doublesided arrow at the header and show the original value from connected query.

Remove the indexes and that´s all.

7 Comments

  1. THANK YOU. I could never think of a way to do this and I need it often!!

  2. Thank you this is great, but I have a question. How could I show the first row? I mean the row with the result 10 in column “Value” and Blank in column “Value from Previous Row”

  3. Is there some reason, why we can´t simply keep the first row?

  4. this is some evil genious man. really pulled me out of a bind, thanks!

  5. Great how to. Thank you. What can I do if my table looks like
    Hamster 10
    Hamster 20
    Hamster 30
    Guinea pig 10
    Guinea pig 20

    Do you recommend separating the Hamster and Guinea pig first or is it possible to add a IF function?

Leave a Reply

Your email address will not be published.

*

clear formPost comment