# How can I do calculation based on next/prior row in Power Query?

• ### Question

• Hi,

Is there any possible to achieve the following statement in Power Query (Query Editor)?

if (Id = Prior/Next row's Id) then (Quantity + Prior/Next row's Quantity)

Thanks.

Regards,

Qilong

Sunday, January 4, 2015 7:38 AM

• You could try adding an index column, and then using the current row's index to look up the value in the prior/next rows.

Ehren

Wednesday, October 28, 2015 10:33 PM
• The code to achieve this is quite obscure.  Assuming you add an Index with the default Step name "Added Index", then on the next step you can refer to the prior row's value from column: "My Column" using:

Monday, November 2, 2015 5:28 AM

### All replies

• You could try adding an index column, and then using the current row's index to look up the value in the prior/next rows.

Ehren

Wednesday, October 28, 2015 10:33 PM
• The code to achieve this is quite obscure.  Assuming you add an Index with the default Step name "Added Index", then on the next step you can refer to the prior row's value from column: "My Column" using:

Monday, November 2, 2015 5:28 AM
• This does not seem to be working.

I am trying to show the previous column Unit Value as a custom column:

=if [Index]=1 then [Unit] else {[Index-1]}[Unit])

But its showing an error,

Details:
Value=List
Key=Unit

Kindly help out.

Monday, November 5, 2018 5:56 PM
• Hi Milay. You're missing the reference to the previous step. It should be before the Index-1 part, like this:

=if [Index]=1 then [Unit] else PreviousStepName{[Index]-1}[Unit]

Ehren

Monday, November 5, 2018 6:00 PM
• In this statement shouldn't {[Index-1]} be {[Index]-1} ??

Also, this is taking a lot of time to load. It's actually going one row at a time and each row is taking 3 seconds to compile. Total dataset is over a million rows.

The statement used is:

Regards,

Milay

• Edited by Monday, November 5, 2018 7:56 PM
Monday, November 5, 2018 7:55 PM
• Monday, November 5, 2018 8:42 PM
• You're correct. I've updated my reply above.

As Anthony said, this approach is not performant for large data sets. For that case, you may want to look into doing this in DAX, after loading the rows to the Data Model.

Ehren

Monday, November 5, 2018 9:06 PM
• Thank you very much Anthony & Ehren! This is exactly what I was looking for.

I knew about merging queries but never realized that I could merge queries by the table itself. So I was able to obtain the previous row values & that simplified the data analysis.

Thanks again!

Regards,

Milay

Tuesday, November 6, 2018 8:45 PM