Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Subtract two rows based on condition

Hello,    I am trying to subtract two consecutive rows from each other (Power) based on a condition from another column (Day No.) to yield a new value (Power_2) as shown below. However, once a new ...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

    Hope this works for you.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcqxDcAwDAPBXVirsKjYyxjaf40EQoDvrrh7lQrlUsfPhIY1NNfc4YZnWB+9YELDgo+6Xw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t, Power = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", Int64.Type}, {"Power", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Power_2", each let _day = [Day], _power = [Power] 
        in [Power] - Replacer.ReplaceValue( List.Max( Table.SelectRows( #"Changed Type", each [Day] = _day and  [Power] < _power )[Power]), null, 0 ), Int64.Type )
in
    #"Added Custom"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi Anonymous 

Hope this works for you.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcqxDcAwDAPBXVirsKjYyxjaf40EQoDvrrh7lQrlUsfPhIY1NNfc4YZnWB+9YELDgo+6Xw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t, Power = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", Int64.Type}, {"Power", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Power_2", each let _day = [Day], _power = [Power] 
        in [Power] - Replacer.ReplaceValue( List.Max( Table.SelectRows( #"Changed Type", each [Day] = _day and  [Power] < _power )[Power]), null, 0 ), Int64.Type )
in
    #"Added Custom"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski