Forum Discussion
Power Query: Computing a value of a column based on the previous value of the same column
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 🙂
Hi Hart1969 ,
Please click "Transform Data" to enter the Power Query Editor, click "Advanced Editor", and paste the following code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkrNSU0sTlXSUfJLTU1JTVFwTixITM4sqQSKOJYlZuYkJuWkIgRjdaKVjIz0DIGyxqYgwgAmZATkmRigCBkDeUaoQiZAniGykDHYLBNTFCEjCA9ZCGwWqioUs2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Release", type number}, {"Needed Capacity", Int64.Type}, {"Available Capacity", Int64.Type}}), test = Table.AddColumn(#"Changed Type1", "Surplus", each [Needed Capacity] - [Available Capacity]), #"Added Index" = Table.AddIndexColumn(test, "Index", 1, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", (x)=>List.Accumulate(List.Range(#"Added Index"[Surplus],0,x[Index]),0,(x,y)=> if x+y <=0 then 0 else x+y ) ), #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Backlog"}}) in #"Renamed Columns"Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Hart1969 ,
Please click "Transform Data" to enter the Power Query Editor, click "Advanced Editor", and paste the following code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkrNSU0sTlXSUfJLTU1JTVFwTixITM4sqQSKOJYlZuYkJuWkIgRjdaKVjIz0DIGyxqYgwgAmZATkmRigCBkDeUaoQiZAniGykDHYLBNTFCEjCA9ZCGwWqioUs2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Release", type number}, {"Needed Capacity", Int64.Type}, {"Available Capacity", Int64.Type}}), test = Table.AddColumn(#"Changed Type1", "Surplus", each [Needed Capacity] - [Available Capacity]), #"Added Index" = Table.AddIndexColumn(test, "Index", 1, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", (x)=>List.Accumulate(List.Range(#"Added Index"[Surplus],0,x[Index]),0,(x,y)=> if x+y <=0 then 0 else x+y ) ), #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Backlog"}}) in #"Renamed Columns"Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.