Refer to Previous Row and Next Row in Power Query

You’ll often run into a need to refer to the previous or the next row of a particular column in Power Query. Since Power Query (M) is quite unless excel you’ll need a bit of M formula language to get this done. Nevertheless it’s not at all difficult.

 

See this data

Have loaded this data in Power Query with just one column – Date

 

Refer to Previous Row in Power Query

Now I want to check if the previous row date is equal to current row date ?

Couple of things to consider.

1. Since the dates may not be sorted, so the first thing to do is add sorting to Dates. Filter Drop Down (Date Column) >> Sort Ascending
2. Also we we don’t have built in row numbers in Power Query, so we’ll need an Index column. Add Column >> Index Column Drop Down >> From 0, there is a reason why we start with 0 and not 1, which I’ll get to in just a moment.

Now the query with sorted Dates and an Index column looks like this..

 

To refer to the previous row add a Custom Column. Add Column >> Custom Column and write the following M Code

= #'Added Index' [Date] {[Index] - 1}

If you notice carefully..

• The above code has 3 parts
  • #”Added Index” – Nothing but the table name (which is your previous step)
  • [Date] – Column Name to be referred
  • { [Index] -1 } – The row number from the Index Column. Since Power Query counting begins from 0 so -1 to go to the previous row.
  • The spaces are intentional to make the code readable, they wont have any impact 😎
• You’ll also see error in the first row since there is no previous row before that. To fix it, you can use “try / otherwise” in Power Query, very similar to IFERROR in Excel

So the new code looks like this and the error is now smoked 😀

= try #'Added Index' [Date] {[Index] - 1} otherwise null

 

Refer to the next row in Power Query

By now you’d have guessed the solution, but I’ll any ways put it down.

1. Again Add Column Tab >> Custom Column
2. And write this code (along with try and otherwise) and of course as you can guess the -1 becomes +1

= try #'Added Index' [Date] { [Index]  + 1 } otherwise null

 

 

Imke (the Power Query genius from biccountant) created a custom function for this, it’s brilliant, you should see it

More question?.. Drop in a comment, be glad to help!

 

More Killer Tricks in Power Query

1. Running Total in Power Query
2. How to write IF in Power Query
3. Dynamic Column Selection in Power Query
4. Separate Numbers from Text