Forum Discussion
Data overflow error when the "List.Accumulate" function was used to process data over 2000 rows
My Source Table:
Source Table
Desired 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"]}[Content],
rows = List.Buffer(Table.ToRows(Source)),
acc = List.Accumulate(
rows,
{"", {}},
(s,c)=>if s{0}="" then {c{0}, {c}}
else if s{0}+1=c{0} then {c{0},s{1}&{c}}
else {c{0}, s{1}&List.Transform({s{0}+1..c{0}-1}, each {_,0})&{c}}
),
result = Table.FromRows(acc{1}, Table.ColumnNames(Source))
in
result
When my data volume is less than 2000 lines, I use the List.Accumulate function to handle, the code is running properly, the data volume is more than 2000 lines, there will be a stack overflow error, Using just the List.Accumulate solution, is there a way to get code to work properly with more than 2,000 lines of data.
Thank in advance
the link to my excel file: Insert Missing SN
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.
>>
20 Replies
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
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.
Hello ziying35
try this approach. Should be faster
let Source = #table ( {"SN","Value"}, { {"0","1"}, {"1","2"}, {"2","3"}, {"3","4"}, {"5","5"}, {"10","6"}, {"18","7"}, {"20","8"}, {"25","9"}, {"10000","10"} } ), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SN", Int64.Type}}), GetListSN = List.Numbers(List.Min(#"Changed Type"[SN]), List.Max(#"Changed Type"[SN])-List.Min(#"Changed Type"[SN])), #"Converted to Table" = Table.FromList(GetListSN, Splitter.SplitByNothing(),1, null, ExtraValues.Error), #"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "SN"}}), Join = Table.NestedJoin(#"Renamed Columns","SN",#"Changed Type","SN","Real SN"), #"Expanded Real SN" = Table.ExpandTableColumn(Join, "Real SN", {"Value"}, {"Value"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Real SN",null,"0",Replacer.ReplaceValue,{"Value"}) in #"Replaced Value"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
Jimmyit is not clear to me where and why the problem of overflow with the list.accumulate function is needed (apart from the one in which an infinite loop is created).
But for the original problem, a faster solution than that using list.accumulate might be to turn the initial table into a record ( i called sorgente) where the SN column are the fields and the Value column are the values.
after with the following expression get the final tableS = Table.FromRows(List.Transform(List.Transform({1..200000},each Text.From(_)),each {_,Record.FieldOrDefault(sorgente,_,"0")}),{"SN","Value"})
