none
Cumulative Sum of weeks upto certain point and then append the remaining weeks summing them cumulatively RRS feed

  • 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 SamNaik Thursday, September 19, 2019 9:58 PM
    Thursday, September 19, 2019 9:57 PM

Answers

  • 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 SamNaik 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 SamNaik 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