# Cumulative Sum of weeks upto certain point and then append the remaining weeks summing them cumulatively • ### Question

• Hi experts

I have the following table:  I would like my output to look like this below: The logic essentially sums "IssueCount" from WK19 upto WK25 which gives the value of 418 and then does a cumulative addition thereon . ie. WK26 = 418+47, WK27 = 465+23 and so on.
There could be weeks prior to WK19 so I dont want to have my marker necessarily at WK19. But the WK25 marker remains .  Any help would be great!

Sam

• Edited by Thursday, September 19, 2019 9:58 PM
Thursday, September 19, 2019 9:57 PM

• Hi Sam,

The code below is one of many approaches to the problem

`let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],    changedType = Table.TransformColumnTypes(Source,{{"Week", type text}, {"IssueCount", Int64.Type}}),    filteredTable1 = Table.SelectRows(changedType, each Number.From(Text.Middle([Week], 2)) <= 25),    issueCountListFiltered1 = List.Buffer(filteredTable1[IssueCount]),    filteredTable2 = Table.SelectRows(changedType, each Number.From(Text.Middle([Week], 2)) > 25),    replacedValues1 = Table.ReplaceValue(        filteredTable1,         each [IssueCount],         each List.Sum(issueCountListFiltered1),        Replacer.ReplaceValue,        {"IssueCount"}    ),    lastRowFiltered1 = Table.LastN(replacedValues1, 1),    appendedTables = Table.Combine({lastRowFiltered1, filteredTable2}),    addedIndex = Table.AddIndexColumn(appendedTables, "Index", 1),    replacedValues2 = Table.ReplaceValue(        addedIndex,        each [IssueCount],        each List.Sum(List.FirstN(addedIndex[IssueCount], [Index])),        Replacer.ReplaceValue,        {"IssueCount"}    ),    removedIndex = Table.RemoveColumns(replacedValues2,{"Index"})in    removedIndex`

• Marked as answer by Friday, September 20, 2019 4:40 AM
Friday, September 20, 2019 12:30 AM

### All replies

• Hi Sam,

The code below is one of many approaches to the problem

`let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],    changedType = Table.TransformColumnTypes(Source,{{"Week", type text}, {"IssueCount", Int64.Type}}),    filteredTable1 = Table.SelectRows(changedType, each Number.From(Text.Middle([Week], 2)) <= 25),    issueCountListFiltered1 = List.Buffer(filteredTable1[IssueCount]),    filteredTable2 = Table.SelectRows(changedType, each Number.From(Text.Middle([Week], 2)) > 25),    replacedValues1 = Table.ReplaceValue(        filteredTable1,         each [IssueCount],         each List.Sum(issueCountListFiltered1),        Replacer.ReplaceValue,        {"IssueCount"}    ),    lastRowFiltered1 = Table.LastN(replacedValues1, 1),    appendedTables = Table.Combine({lastRowFiltered1, filteredTable2}),    addedIndex = Table.AddIndexColumn(appendedTables, "Index", 1),    replacedValues2 = Table.ReplaceValue(        addedIndex,        each [IssueCount],        each List.Sum(List.FirstN(addedIndex[IssueCount], [Index])),        Replacer.ReplaceValue,        {"IssueCount"}    ),    removedIndex = Table.RemoveColumns(replacedValues2,{"Index"})in    removedIndex`

• Marked as answer by Friday, September 20, 2019 4:40 AM
Friday, September 20, 2019 12:30 AM
• Perfect solution ! Thanks Colin !
Friday, September 20, 2019 4:40 AM