Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Dicken
Post Patron
Post Patron

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

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super 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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

5 REPLIES 5
Dicken
Post Patron
Post Patron

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

Vijay_A_Verma
Super User
Super User

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
Super User
Super User

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

 {List.First(alist)} & gen
OwenAuger
Super User
Super 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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

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. 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors