Forum Discussion
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),
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
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
5 Replies
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 CumulativeListReally 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.
If the first item in the list will always be the one you need replace, you can use:
{List.First(alist)} & genOne 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_TotalThanks I have not had a proper chance to work through it all but it looks good.
