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.

    >>

     

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.

     

     

     

     

    • ziying35's avatar
      ziying35
      Impactful Individual
      6 years ago

      Anonymous 

      The file I uploaded to cloud disk is some data I simulated. The data volume is more than 190,000 lines, with no null value, I asked a few friends to use a higher configuration of the computer in my file to test your code, although it will not make mistakes, but there is no way to load out the data