Hi I am trying to create a column "Value to Replace" based on the value in the "RunningTota"l column, if there is a negative value. I think?? I need to try and create a list from the "Delivery qty" column that is => then the negative number then sum this list and add to the new column. starting the calc again when a negative number results in the "RunningTotal" Column
eg. Always starting from the first Row in "Stock" I subtract "Order qty" from Stock 14 - 10 = 4 is returned in the "RunningTotal" column.
The second row "Qty required" is 10 again so 10 -4 = -6 Know I need to create a list generated from the "Delivery qty" which is the first sum greater or equal to the "RunningTotal" -6 would require (4+10) = 14 this is then added to the "RunningTotal" result is 8.
The "Qty required" is now subtracted from this value 8 - 6 = 2 which is returned into the "RunningTotal" Column and the sequence continues using the next values in the "Delivery qty" column when required by a negative number in the "RunningTotal"
Any help with this would be greatly appreciated as It really is stretching my basic Power Query knowledge.
eg. Always starting from the first Row in "Stock" I subtract "Order qty" from Stock 14 - 10 = 4 is returned in the "RunningTotal" column.
The second row "Qty required" is 10 again so 10 -4 = -6 Know I need to create a list generated from the "Delivery qty" which is the first sum greater or equal to the "RunningTotal" -6 would require (4+10) = 14 this is then added to the "RunningTotal" result is 8.
The "Qty required" is now subtracted from this value 8 - 6 = 2 which is returned into the "RunningTotal" Column and the sequence continues using the next values in the "Delivery qty" column when required by a negative number in the "RunningTotal"
Any help with this would be greatly appreciated as It really is stretching my basic Power Query knowledge.
Qty required | RunningTotal | Value to Replace | Stock | Delivery qty |
10 | 4 | 14 | 0 | |
10 | -6 | 8 | 14 | 0 |
6 | 2 | 14 | 4 | |
4 | -2 | 4 | 14 | 10 |
3 | 1 | 14 | 6 | |
2 | -1 | 2 | 14 | 3 |
2 | 0 | 14 | 0 | |
2 | -2 | 14 | 0= |