Recursive / Iterative Calculation in Power Query

[Anonymous]

Hello Everybody,

I need your help solving a problem.

I am trying to calculate a new value of a row using the previous value of the row.

My attempts using Dax ended all in circular dependancys.

My hope is, that what I want to do is possible in Power Query using M.

I constructed following Query/Table:

BOZ1 is some historical data, this column will not change with refresh and no new values will be added.

In order to calculate the NewValue from 24/11/20 and onward I need to take the 144 as a starting point and subtract the value in VOZ: 144 - 0,46 = 143,54. This will be my value for 24/11/20. 

The Value for 25/11/20 is 143,54-0 = 143,54 and so on.

I am not sure how to proceed.

With the help of Google I figured out two possible solution:

1. Write a loop as a custom function and invoke it to add a new column

or

2. Use this solution: https://stackoverflow.com/questions/57460424/iterative-calculation-in-power-query

Both solutions are high above my current skillset.

I hope somebody can give me some assistance, as this problem is really keeping me up at night.

Best Regards

Rain

[Jimmy801]

Hello @Anonymous 

you can apply a Table.Distinct on your Index-column and then add this formula to a custom column

try RemovedDuplicates{[Index= [Index]-1]}[BOZ1] - [VOZ Sys1] otherwise null

Here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRMzTUMzIwMlDSUTKzNAaSQGSgY2JmCARKsTpAJcYoSoyApKGJCUhhbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum = _t, Index = _t, BOZ1 = _t, #"VOZ Sys1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type date}, {"BOZ1", Int64.Type}, {"VOZ Sys1", type number}, {"Index", Int64.Type}}, "de-DE"),
    RemovedDuplicates = Table.Buffer(Table.Distinct(#"Changed Type", {"Index"})),
    #"Added Custom" = Table.AddColumn(RemovedDuplicates, "Result", each  try RemovedDuplicates{[Index= [Index]-1]}[BOZ1] - [VOZ Sys1] otherwise null )
in
    #"Added Custom"

The performance of this should be also very good

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ImkeF · Accepted solution]

Hi @Anonymous ,

please paste the following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRMzTUMzIwMlDSUTKzNAaSQGSgZ2JmCARKsTpAJcYoSoyApKGJCUhhbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum = _t, Index = _t, BOZ1 = _t, #"VOZ Sys1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type date}, {"BOZ1", Int64.Type}, {"VOZ Sys1", type number}, {"Index", Int64.Type}}),
    BufferedStart = Table.Buffer( Table.Sort(#"Changed Type",{{"Index", Order.Ascending}}) ),
    Initial = BufferedStart{0},
    ListGenerate = List.Generate( ()=>
    [Result = Initial[BOZ1], Counter = 0],
    each [Counter] < Table.RowCount(BufferedStart),
    each [
        Result = [Result] - BufferedStart[VOZ Sys1]{Counter},
        Counter = [Counter] + 1
    ],
    each [Result]
),
    Custom1 = Table.FromColumns(Table.ToColumns(BufferedStart) & {ListGenerate} , Table.ColumnNames(BufferedStart) & {"Result"})
in
    Custom1

To adapt it to your solution, you have to replace the code in the first step (Source) to a reference to oyur table.

[Anonymous]

Hi Imke,

thank you very much, your solution worked like a charm! 

I still have to understand the solution as a whole, but beeing able to implement it is a good starting point.

Best Regards

Vanessa