• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create a List from a column in Power Query based on a value in another.

stubrg

New Member
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.





Qty requiredRunningTotalValue to ReplaceStockDelivery 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=​
 
Back
Top