Power Query Accumulate with text in list

[Dicken]

Hi, 
 is there another way to deal with an accumulation when text values appear on the list ; I have broken and then used insert ; 
let
alist = {"A", 2, 3, 4, 3, 2, 3, 2},
slist = List.Skip(alist, 1),
gen = List.Generate(() => [x = 0, y = slist{0}],
each [x] < List.Count(slist),
each [x = [x] + 1, y = [y] + slist{x}],
each [y]
)
in
List.InsertRange(gen, 0, List.FirstN(alist, 1))

I don't know if you could put it back together in more effecient way or perhaps not break up in the first place  I 
have tried a try / othewise but not got it working as insert would not be great in the text appeared and some random point. 

Richard
i

[Dicken]

Thanks I have not had a proper chance to work through it all but it looks good. 

[Vijay_A_Verma]

One alternative

let
    alist = {"A", 2, 3, 4, "B", 3, 2, "C", 3, 2, "D"},
    BuffList = List.Buffer(alist),
    Running_Total = List.Generate(()=>[x=BuffList{0}, y=List.Buffer(List.Transform(BuffList, (x)=> if Value.FromText(x) is number then x else 0)), i=0], each [i]<List.Count([y]), each [i=[i]+1, x=if Value.FromText(BuffList{i}) is text then BuffList{i} else List.Sum(List.FirstN(y, i+1)), y = [y]], each [x])
in
    Running_Total

[ronrsnfld]

If the first item in the list will always be the one you need replace, you can use:

 {List.First(alist)} & gen

[OwenAuger · Accepted solution]

Hi @Dicken 

Is it possible that text values can be anywhere in the list, and should all be ignored for the cumulative sum?

e.g.

This:

{"A", 2, 3, "B", 4, 3, 2, 3, "C", 2}

becomes this:

{"A", 2, 5, "B", 9, 12, 14, 17, "C", 19}

Here is an example of how I might handle this more general case (as well as your original example):

let
  alist = {"A", 2, 3, "B", 4, 3, 2, 3, "C", 2},
  CumulativeList = List.Generate(
    () => [
      Index          = 0,
      Value          = alist{0},
      IsNumber       = Value.Is(Value, type nullable number),
      NumericalValue = if IsNumber then Value else 0,
      CumulativeSum  = NumericalValue,
      DisplayValue   = if IsNumber then CumulativeSum else Value
    ],
    each [Index] < List.Count(alist),
    each [
      Index          = [Index] + 1,
      Value          = alist{Index},
      IsNumber       = Value.Is(Value, type nullable number),
      NumericalValue = if IsNumber then Value else 0,
      CumulativeSum  = [CumulativeSum] + NumericalValue,
      DisplayValue   = if IsNumber then CumulativeSum else Value
    ],
    each [DisplayValue]
  )
in
  CumulativeList

[Dicken]

Really very impressed with this, I have not got all quite figured but have if nothing else have learned 
Value.Is , as I would have used Value.Type ( x) = type , which has led to me finding Value.As , 

is there not end to m functions.