Forum Discussion

AGo's avatar
AGo
Post Patron
6 years ago
Solved

Conditional efficient rolling sum function

Hi! I combined a lot of csvs now I have to calculate (in M language because I'll have to pivot a column next) a new column which increases by 1 after every time [Stop] value is 1. In Calc1 there's t...
Need Help
Tips and Tricks
Tutorial Requests
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi AGo 

    please check out this solution (paste the code into the advanced editor and follow the steps):

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1SGXNKRAL9XNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Stop = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stop", Int64.Type}}),
    Custom1 = List.Buffer( #"Changed Type"[Stop] ),
    Calc1 = List.Generate( ()=>
        [Level = 0, Counter = 0],
        each [Counter] < List.Count(Custom1) ,
        each [
            Level = if Custom1{[Counter]} = 1 then [Level] + 1 else [Level],
            Counter = [Counter] + 1
        ],
        each [Level] ),
    AutomaticConversionWithFullTable = Table.FromColumns( Table.ToColumns(#"Changed Type") & {#"Calc1"}, Table.ColumnNames(#"Changed Type") & {"Calc1"} )
in
    AutomaticConversionWithFullTable

     

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi AGo 

    please check out this solution (paste the code into the advanced editor and follow the steps):

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1SGXNKRAL9XNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Stop = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stop", Int64.Type}}),
    Custom1 = List.Buffer( #"Changed Type"[Stop] ),
    Calc1 = List.Generate( ()=>
        [Level = 0, Counter = 0],
        each [Counter] < List.Count(Custom1) ,
        each [
            Level = if Custom1{[Counter]} = 1 then [Level] + 1 else [Level],
            Counter = [Counter] + 1
        ],
        each [Level] ),
    AutomaticConversionWithFullTable = Table.FromColumns( Table.ToColumns(#"Changed Type") & {#"Calc1"}, Table.ColumnNames(#"Changed Type") & {"Calc1"} )
in
    AutomaticConversionWithFullTable

     

    • AGo's avatar
      AGo
      Post Patron
      6 years ago

      Hi ImkeF ,

      at first your solution worked but caused the error "Expression.Error: Evaluation resulted in a stack overflow and cannot continue." right after using the pivoting function on another column of the same table (in don't aggregate mode).

      Then I used your function in the example file transformation step before combining multiple files, I concatenated your result with the column with file name, so it worked.

      I don't know why it returned that error, maybe this function is too heavy for massive calculation after files combining.

       

      Thanks