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
OwenAuger
1 year agoSuper User
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
- Dicken1 year agoPost Prodigy
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.