Forum Discussion
Dicken
1 year agoPost Prodigy
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...
- 1 year ago
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
ronrsnfld
1 year agoSuper User
If the first item in the list will always be the one you need replace, you can use:
{List.First(alist)} & gen