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.
Thanks for this!!!
THANK YOU. I could never think of a way to do this and I need it often!!
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”
Is there some reason, why we can´t simply keep the first row?
this is some evil genious man. really pulled me out of a bind, thanks!
Saved my life! 🙂
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?