Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Recursive / Iterative Calculation in Power Query

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

Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    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.

     

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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