Power Query: Computing a value of a column based on the previous value of the same column

[Hart1969]

Hello,

i have the following problem and need a solution to solve it with Power Query:

Ich have different Releases (22.1, 22.2, 22.3 , 22.4, 23.1, 23.2, 23.3, 23.4)

In each release i the given the needed capacity and the available capacity.

Now i want to compute the surplus and the backlog for these capacities:

The surplus is just [needed capacity] - [available capacity]

The backlog must add the backlog value from the previous step + surplus. If this is negative it must be set to 0

Example:

Release Needed Capacity Available Capacity Surplus Backlog Description
22.1 35 30 5 5 5 day stay in the backlog because the available capicity is lower than the needed capacity
22.2 40 30 10 15 10 days from current release + 5 days from backlog previous step
22.3 20 30 -10 5 The backlog is reduced by 10 days
22.4 10 30 -20 0 The backlog is reduceds to 0 because 20 days are left in the release but only 5 day were in the backlog
23.1 45 30 15 15 15 days from this release go into the backlog
23.2 30 30 0 15 15 days stay in the backlog
23.3 25 30 -5 10 5 days in this release are available to reducing the backlog
23.4 10 30 -20 0 20 days are available in this release reducing the backlog to 0

So my question is: How to solve this in Power Query (compution the backlog column based on value of the previous backlog value)

I'm looking forward to your replies 🙂

[Hart1969]

Cool, thanks for this solution

[lbendlin · Accepted solution]

Power Query has no concept of "previous".  You need to indicate what that means in your scenario, ideally by adding an index column. You can drop the columns that are not part of the issue.

From there it is a standard List.Accumulate to calculate the backlog value:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSM1TSUTI2BREGSrE6YCEjIM/EAEXIGMgzQhUyAfIMkYWMwWaZmKIIGUF4yEJgs1BVoZgVCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Release = _t, #"Needed Capacity" = _t, #"Available Capacity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Needed Capacity", Int64.Type}, {"Available Capacity", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Backlog", each List.Accumulate({0..[Index]},0,(state,current)=> List.Max({0, state + #"Added Index"[Needed Capacity]{current}-#"Added Index"[Available Capacity]{current}})))
in
    #"Added Custom"