Cumulative list that re starts when a text value is encountered.

[Dicken]

Using  list.Generate, and having the list start with and contain text ,   i have come up  with this 
but woudl be interested in other ways espcially if try could be avoided and replaced with a let  statement ; 

let alist = {"A",3,3,2,"a","b",3,3,4,4,"c",2,3,3} 
in
List.Generate( ()=> [ x = 0 , y = alist {0}   ,  z = y ] ,
each [x] < List.Count( alist),
each [ x = [x] + 1,   y = alist{x} ,    z =try [y] + y otherwise y ],
each [z] )

Ricahrd 

[jgeddes · Accepted solution]

I would agree with the comments @lbendlin made to your original code. But if you want to try something different...

List.Generate( ()=> [ iteration=0, iterationType=Value.Is(alist{0}, Text.Type), lastText = 0, currentValue = alist{0} ], each [iteration] < List.Count(alist), each [ iteration = [iteration]+1, iterationType = Value.Is(alist{iteration}, Text.Type), lastText = if iterationType then iteration else [lastText], currentValue = if iterationType or iteration = lastText + 1 then alist{iteration} else [currentValue] + alist{iteration} ], each [currentValue] )

[ThxAlot · Accepted solution]

Easy enough,

let Source = {"A",3,3,3,"a","b",2,3,4,3,2,"c",2,3,3,3} in List.Accumulate(Source, {}, (s,c) => s & {try List.Last(s)+c otherwise c})

[SundarRaj]

Hi @Dicken., I would agree with all the posts here but this is a different approach to this solution. I'll attach the images of the output and M code used. Thanks!

Here's the code:

let
Source = {"A",3,3,2,"a","b",3,3,4,4,"c",2,3,3},
Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
Rename = Table.RenameColumns(Table,{{"Column1", "Sum"}}),
Grouped = Table.Group(Rename, {"Sum"}, {{"All", each _[Sum], Int64.Type}},GroupKind.Local,(x,y) => Number.From( y[Sum] is text )),
All = Table.TransformColumns(Grouped, {"All", each List.Select(_, each not (try Number.From(_))[HasError])}),
Sum = Table.TransformColumns(All,{"All", each List.Skip(List.Accumulate(_, {0}, (s,c) => s & {List.Last(s) + c}))}),
Lists = Table.TransformColumns(Sum,{"Sum", each {_}}),
Merged = List.Transform(Table.ToRows(Lists), each List.Combine(_)),
Combined = List.Combine(Merged)
in
Combined

[Dicken]

Like the idea, 
Im' going ot have to study that, 
I've got the grouping, Im not greae on custom comparers; 
but 
= Table.Group(Source, {"Unit"}, {{"Count", each
[Unit] }}, 0 , (x,y)=> Number.From(Value.Is( y [Unit] ,type text ) ))

and then thought within the grouping use something similar such as ; 

let alist = {"a",2,3,3,"a","b",3,3}
in
List.Accumulate( alist, {} , (s,c)=>s & { try (List.Last(s)??0 ) + c otherwise c } ) 

but of coursse the above still uses try . 

Richard 

I'll leave it open as I would be 

[lbendlin]

Not sure what your ultimate goal is. If you want to avoid the try ... otherwise ... approach (why?)  then use Value.Is() to sense the value type.

[v-dineshya · Accepted solution]

Hi @Dicken ,

Thank you for reaching out to the Microsoft Community Forum.,

Your current method uses try to handle the switching between number accumulation and text resets, which maybe not as readable or clean as it could be.

Here’s an alternate version using let inside List.Generate to replace try, making the logic a bit cleaner and more explicit:

let
alist = {"A", 3, 3, 2, "a", "b", 3, 3, 4, 4, "c", 2, 3, 3},
result = List.Generate(
()=> [x = 0, acc = 0, y = alist{0}, z = if Value.Is(alist{0}, type number) then alist{0} else alist{0}],
each [x] < List.Count(alist),
each
let
nextX = [x] + 1,
nextY = alist{nextX},
isNumber = Value.Is(nextY, type number),
nextAcc = if isNumber then if Value.Is([y], type number) then [acc] + nextY else nextY else 0,
nextZ = if isNumber then nextAcc else nextY
in
[x = nextX, y = nextY, acc = if isNumber then nextAcc else 0, z = nextZ],
each [z]
)
in
result

Note: acc is the accumulator for numbers. Resets acc to 0 whenever a text is encountered. Uses Value.Is(..., type number) instead of try to test for numbers. This version avoids try entirely and leans into let for logic clarity.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[ThxAlot · Accepted solution]

Easy enough,

let Source = {"A",3,3,3,"a","b",2,3,4,3,2,"c",2,3,3,3} in List.Accumulate(Source, {}, (s,c) => s & {try List.Last(s)+c otherwise c})

[Dicken]

I did say using Generate not accumulate and NOT using try otherwise 

[lbendlin]

List.Generate( () => [x = 0, y = Source{0}], each [x] < lc, each [x = [x] + 1, y = if Value.Is(Source{x},type text) or Value.Is([y],type text) then Source{x} else [y] + Source{x}], each [y] )

avoiding the try...otherwise...  for no good reason

[lbendlin]

yes, you did say that. But the solution from Sir @ThxAlot is much more elegant .  Apart from a small-ish bug when the list starts with a number  In that case you need to use

let Source = {"A",3,3,3,"a","b",2,3,4,3,2,"c",2,3,3,3} in List.Accumulate(Source, {}, (s,c) => s & {try (List.Last(s) ?? 0)+c otherwise c})

[jgeddes · Accepted solution]

I would agree with the comments @lbendlin made to your original code. But if you want to try something different...

List.Generate( ()=> [ iteration=0, iterationType=Value.Is(alist{0}, Text.Type), lastText = 0, currentValue = alist{0} ], each [iteration] < List.Count(alist), each [ iteration = [iteration]+1, iterationType = Value.Is(alist{iteration}, Text.Type), lastText = if iterationType then iteration else [lastText], currentValue = if iterationType or iteration = lastText + 1 then alist{iteration} else [currentValue] + alist{iteration} ], each [currentValue] )

[Dicken]

I really like this generate method , I have it in notepad and taking apart. 
Thanks. 

Richard. 

[Dicken]

I think I have enough to be working on for now thank you for all responses. 
Richad. 

[lbendlin]

Shouldn't the result for row 4 be 8 ?

[Dicken]

Try this, 
let alist = { "A",3,3,3,"a","b",2,3,4,3,2,"c",2,3,3,3}
in
List.Generate( ()=> [ x = 0 , y = alist{0} ] ,
each [x] < List.Count( alist) ,
each [ x = [x] + 1, y = try [y] + alist {x} otherwise
alist{x} ] ,
each [y] )

[lbendlin]

How long is your actual list? Are you concerned about performance or code size?

[Dicken]

Obviuosly I'd like it as effecient as possible, but Im just interested in alternatvie approaches, 
List.accumulate can be easier, but Generate is generally more effecient . 

[Dicken]

No, not really. 

[lbendlin]

then go with your actual code.  Maybe throw in a List.Buffer just for fun, and pull the list count out of the loop.

let alist = List.Buffer({"A", 3, 3, 3, "a", "b", 2, 3, 4, 3, 2, "c", 2, 3, 3, 3}), lc = List.Count(alist) in List.Generate( () => [x = 0, y = alist{0}], each [x] < lc, each [x = [x] + 1, y = try [y] + alist{x} otherwise alist{x}], each [y] )

[Dicken]

Yes, I'm sure I'd got it working, will re check.