 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}),
replacedValues2 = Table.ReplaceValue(
each [IssueCount],
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}),
replacedValues2 = Table.ReplaceValue(
each [IssueCount],
• 