Math operation on different rows

[zagger]

Hi All,

I am trying to calculate one new column based on the above row. The problem that I am not able to solve is I have to get the number for example of August from the same column July data. And when I have the number for August then I have to get September data from that calculated August data in the same command. Please see the image below:

As you can see I already have the number for July month and August is null. The number that I want in August is the growth rate in August column with the July number which will be : (874544*0.062921613+874544)

When I get the August data then I have to use the same logic to get subsequent months data. Is it possible to do it in the same command or would I need seperate quesries for each month? I tried one way to get for each month but it made the workbook very heavy and almost impossible to run. Please let me know if there is efficient code for this. 

Many many thanks!

[CNENFRNL]

Add a index column in order to pick up data of previous row.

[zagger]

Tried that, but will have to keep adding for every month and then consolidate all months columns to one final column. It made the workbook heavy and it crashed. 

[v-yingjl · Accepted solution]

Hi @zagger ,

If your source table is too large and need to refer multiple times, you can consider use Table.buffer() to store the table in memory to improve the performance. 

If every time a table is transformed resulting in a new table, you'd need to use Table.Buffer again.

= Table.Buffer (Table.AddIndexColumn(laststep, "Index", 0, 1) ) 

You can also refer these simliar issues:

1. Using Table.Buffer 
2. How to use Table.Buffer and how does it work? 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.