Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"]}[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
Solved! Go to Solution.
<<...
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.
>>
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.