Forum Discussion
Solved
Power Query Accumulate with text in list
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...
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
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