Value from previous row – Power Query, M language

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.