none
Power query running total with multipe sum values RRS feed

  • Question

  • I'm a Power Query neophyte who is look for a simple solution to a running total problem. I found a post on this forum which was very helpful in solving the problem for a single set (column) of values. But I have a scenario where I want the sum of several values based on a singe grouping. Here is an example

    Here is a sample of the data

    Year Period Index EI CPP Tax
    2017 201721 9.38 $28.47 $11.47
    2017 201722 10.81 $32.82 $13.22
    2017 201723 11.97 $38.83 $14.65
    2017 201724 6.41 $19.47 $7.84
    2017 201725 6.3 $21.62 $7.71
    2017 201726 5.88 $17.86 $7.19
    2018 201801 5.26 $15.69 $6.32
    2018 201802 1.6 $4.76 $8.24

    Here is the wanted result / output

    Year Period Index EI YTD CPP YTD Tax YTD
    2017 201721 $9.38 $28.47 $11.47
    2017 201722 $20.19 $61.29 $24.69
    2017 201723 $32.16 $100.12 $39.34
    2017 201724 $38.57 $119.59 $47.18
    2017 201725 $44.87 $141.21 $54.89
    2017 201726 $50.75 $159.07 $62.08
    2018 201801 $5.26 $15.69 $6.32
    2018 201802 $6.32 $20.45 $8.24

    I have been able to create a query (with help from this forum) to produce the desired result for the first value column EI YTD, but not for the other values... CPP YTD and Tax YTD. Here is the query I am using.

    let
        // function based on List.Generate
        fxRunningTotalLG = (tab as table, col_name as text) =>
         
          let
             LTS = List.Buffer(Table.Column(tab, col_name)),
             ListCount = List.Count(LTS),
             LstGen = List.Generate(()=>
                    [Counter=1, RT= LTS{0}],
               each [Counter]<=ListCount,
               each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
               each [RT]),
             LstHeadres = List.Combine({Table.ColumnNames(tab), {"col_name"}}),
             TblToCol = Table.FromColumns(List.Combine({Table.ToColumns(tab), {LstGen}}), LstHeadres)
          in
             TblToCol,

    // choose column to grouping and to sum
        CFT = "Year", // column name for grouping
        CFRT = "EI",// column name to sum
      
         Source = Excel.CurrentWorkbook(){[Name="Earnings"]}[Content],
         GrupCol = Table.Group(Source, {CFT},  {{"tab", each _, type table}}),
         RemCol = Table.SelectColumns(GrupCol,{"tab"}),
         TransfCol = Table.TransformColumns(RemCol, {"tab", each fxRunningTotalLG(_, CFRT)}),
         ExpCol = Table.ExpandTableColumn(TransfCol, "tab",{"Year","Period Index", "col_name"},{"Year","Period Index","EI YTD"})
    in
        ExpCol

    Any help would be much appreciated.

         

    Thursday, February 8, 2018 1:09 AM

Answers

  • Hi there,

    It occurred to me that in my "Source" and subsequent steps, I was using the column names from the final table and not the original table (without the YTD suffixes). So if you pasted the steps I provided, the solution won't work because of the column name mismatches.

    Here's the updated script. If it is still not working for you (and you don't have sufficient knowledge to adjust the steps if there is some stuff that don't match exactly with your table), I will try and post a link with my working solution.

    let
        Source = Excel.CurrentWorkbook(){[Name="Earnings"]}[Content],
        GroupedRows = Table.Group(Source, {"Year"}, {{"Table", each _, type table}}),
        AddedIndex = Table.TransformColumns(GroupedRows, {"Table", (row) => Table.AddIndexColumn(row, "Index", 1, 1)}),
        RunningTotal_EI_YTD = Table.TransformColumns(
                                  AddedIndex, 
                                  {"Table", (row) => Table.ReplaceValue(row, each [EI], each List.Sum(List.FirstN(row[EI], [Index])), Replacer.ReplaceValue, {"EI"})}
                              ),
        RunningTotal_CPP_YTD = Table.TransformColumns(
                                  RunningTotal_EI_YTD,
                                  {"Table", (row) => Table.ReplaceValue(row, each [CPP], each List.Sum(List.FirstN(row[CPP], [Index])), Replacer.ReplaceValue, {"CPP"})}
                               ),
        RunningTotal_Tax_YTD = Table.TransformColumns(
                                   RunningTotal_CPP_YTD,                
                                   {"Table", (row) => Table.ReplaceValue(row, each [Tax], each List.Sum(List.FirstN(row[Tax], [Index])), Replacer.ReplaceValue, {"Tax"})}
                               ),
        ExpandedTable = Table.ExpandTableColumn(RunningTotal_Tax_YTD, "Table", {"Period Index", "EI", "CPP", "Tax"}, {"Period Index", "EI YTD", "CPP YTD", "Tax YTD"}),
        ChangedType = Table.TransformColumnTypes(ExpandedTable,{{"Year", Int64.Type}, {"Period Index", Int64.Type}, {"EI YTD", type number}, {"CPP YTD", type number}, {"Tax YTD", type number}})
    in
        ChangedType

    Thursday, February 8, 2018 4:56 PM

All replies

  • Try the following. Grouping is by "Year" based on your sample data:

        Source = Excel.CurrentWorkbook(){[Name="Earnings"]}[Content],
        GroupedRows = Table.Group(Source, {"Year"}, {{"Table", each _, type table}}),
        AddedIndex = Table.TransformColumns(GroupedRows, {"Table", (row) => Table.AddIndexColumn(row, "Index", 1, 1)}),
        RunningTotal_EI_YTD = Table.TransformColumns(
                                  AddedIndex,
                                  {"Table", (row) => Table.ReplaceValue(row, each [EI YTD], each List.Sum(List.FirstN(row[EI YTD], [Index])), Replacer.ReplaceValue, {"EI YTD"})}
                              ),
        RunningTotal_CPP_YTD = Table.TransformColumns(
                                  RunningTotal_EI_YTD,
                                  {"Table", (row) => Table.ReplaceValue(row, each [CPP YTD], each List.Sum(List.FirstN(row[CPP YTD], [Index])), Replacer.ReplaceValue, {"CPP YTD"})}
                               ),
        RunningTotal_Tax_YTD = Table.TransformColumns(
                                   RunningTotal_CPP_YTD,               
                                   {"Table", (row) => Table.ReplaceValue(row, each [Tax YTD], each List.Sum(List.FirstN(row[Tax YTD], [Index])), Replacer.ReplaceValue, {"Tax YTD"})}
                               ),
        ExpandedTable = Table.ExpandTableColumn(RunningTotal_Tax_YTD, "Table", {"Period Index", "EI YTD", "CPP YTD", "Tax YTD", "Index"}),
        RemovedIndex = Table.RemoveColumns(ExpandedTable,{"Index"})
    in
        RemovedIndex



    Thursday, February 8, 2018 5:15 AM
  • Hey Colin,

    Thanks much for your solution. Unfortunately the results are not as expected. All YTD values come up as null in the power query. Perhaps a combination of your code and the running total function in the original post is required?

    Thursday, February 8, 2018 11:48 AM
  • Hi there,

    It occurred to me that in my "Source" and subsequent steps, I was using the column names from the final table and not the original table (without the YTD suffixes). So if you pasted the steps I provided, the solution won't work because of the column name mismatches.

    Here's the updated script. If it is still not working for you (and you don't have sufficient knowledge to adjust the steps if there is some stuff that don't match exactly with your table), I will try and post a link with my working solution.

    let
        Source = Excel.CurrentWorkbook(){[Name="Earnings"]}[Content],
        GroupedRows = Table.Group(Source, {"Year"}, {{"Table", each _, type table}}),
        AddedIndex = Table.TransformColumns(GroupedRows, {"Table", (row) => Table.AddIndexColumn(row, "Index", 1, 1)}),
        RunningTotal_EI_YTD = Table.TransformColumns(
                                  AddedIndex, 
                                  {"Table", (row) => Table.ReplaceValue(row, each [EI], each List.Sum(List.FirstN(row[EI], [Index])), Replacer.ReplaceValue, {"EI"})}
                              ),
        RunningTotal_CPP_YTD = Table.TransformColumns(
                                  RunningTotal_EI_YTD,
                                  {"Table", (row) => Table.ReplaceValue(row, each [CPP], each List.Sum(List.FirstN(row[CPP], [Index])), Replacer.ReplaceValue, {"CPP"})}
                               ),
        RunningTotal_Tax_YTD = Table.TransformColumns(
                                   RunningTotal_CPP_YTD,                
                                   {"Table", (row) => Table.ReplaceValue(row, each [Tax], each List.Sum(List.FirstN(row[Tax], [Index])), Replacer.ReplaceValue, {"Tax"})}
                               ),
        ExpandedTable = Table.ExpandTableColumn(RunningTotal_Tax_YTD, "Table", {"Period Index", "EI", "CPP", "Tax"}, {"Period Index", "EI YTD", "CPP YTD", "Tax YTD"}),
        ChangedType = Table.TransformColumnTypes(ExpandedTable,{{"Year", Int64.Type}, {"Period Index", Int64.Type}, {"EI YTD", type number}, {"CPP YTD", type number}, {"Tax YTD", type number}})
    in
        ChangedType

    Thursday, February 8, 2018 4:56 PM
  • It works perfectly! Thanks very much. I'm on to my next challenge...
    Thursday, February 8, 2018 11:11 PM