Forum Discussion

ziying35's avatar
ziying35
Impactful Individual
6 years ago
Solved

Data overflow error when the "List.Accumulate" function was used to process data over 2000 rows

My Source Table:   Desired Table: Based on the Source Table, insert the missing "SN" with a value of 0, My M code as below: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Curt Hagenlocher's argument   seems interesting to me.

    <<...

    But broadly speaking, M is a lazy language and some of the things you compute actually result in promises instead of immediate values. If the combination function passed to List.Accumulate results in a promise, then the result of accumulating over your list is a promise on top of a promise on top of... etc. At some point, when the actual value is calculated, it's done recursively -- the chain of promises is too long and the stack flows over.

    >>

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I don't think the problem is in the size of the input table.

 

You should check if you have some null value in the SN column.

 

Seems to me that you initial if construct a sort of recursive loop that never ends.

 

 

  • ziying35's avatar
    ziying35
    Impactful Individual
    6 years ago

    Anonymous 

    This is just one of my cases, and there are many others that have this problem. I see that you often use List.Accumulate function to help others provide solutions, you can try to increase the data volume of those cases, I am sure that the same problem will occur

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi ziying35 ,

      In fact, I have never experienced such a situation and I cannot absolutely exclude for all the solutions I have proposed using constructs that create loops something like this may happen.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        in your you could try this

         

            S = Table.Buffer(Table.RemoveMatchingRows(Source, {[SN=null]},"SN")),
    acc = List.Accumulate(
              {1..Table.RowCount(S)-1},
              {Record.FieldValues(S{0})},
              (s,c)=>if S{c}[SN]=S{c-1}[SN]+1 then s&{Record.FieldValues(S{c})}
                          else s&List.Transform({S{c-1}[SN]+1..S{c}[SN]-1}, each {_,0})&{Record.FieldValues(S{c})}
          ),
          result=Table.FromRows(acc,{"SN","Value"})
in
   result

         

        or this

         

            S = Table.Buffer(Table.RemoveMatchingRows(Source, {[SN=null]},"SN")),
    acc = List.Accumulate(
              {1..Table.RowCount(S)-1},
              {S{0}},
              (s,c)=>if S{c}[SN]=S{c-1}[SN]+1 then s&{S{c}}
                          else s&List.Transform({S{c-1}[SN]+1..S{c}[SN]-1}, each [SN=_,Value=0])&{S{c}}
          ),
          result=Table.FromRecords(acc)
in
   result

         

         

         

         

        but, I'm sure, you don't need any help on this aspect.

        I proposed a solution only to partecipate at the discussion.