How to change Running Total from starting at the top of the group to starting at the bottom.

[JenSme]

Please help. I am new to Power Query, using M Code language in excel. I have data that is being pulled over from another source, and will update upon refresh, so using the INDEX/MATCH, and SUMIF in excel does not work, as the cell numbers change as the data updates. I have been able to pull the data into Power Query and create the running total, but my issue is that the running total starts at the top and then will sum downwards. I have the data separated in groups dependent on specific outs for that segment, and I need the running total to start at the bottom and sum upwards. I have tried adding an INDEX and having the index start at one and decrease by 1 but I cannot get that to change the running total. I have looked up reverse running total, but that is not what I am looking for either. I am needing the running total to start at the end of the group and then add upwards throughout the group. 

EX of how the data would be grouped by seg outs and then running total adding from the bottom of the grouping to the top. 

[dufoq3]

Hi @JenSme, check this:

Result

let
    fnRunningTotal = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[#"Seg Outs"=7000]}[All],
            _Detail = myTable,
            _BufferedAmount = List.Buffer(_Detail[Amount]),
            _lg = List.Generate(
                ()=> [ x = List.Count(_BufferedAmount)-1, y = _BufferedAmount{x} ],
                each [x] >= 0,
                each [ x = [x]-1, y = [y] + _BufferedAmount{x} ],
                each [y]
            ),
            _ToTable = Table.FromColumns(Table.ToColumns(_Detail) & {List.Reverse(_lg)}, Value.Type(_Detail & #table(type table[Running Total=number], {})))
        ][_ToTable],

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMFDSUTI1UIrVgfMMDVC4plCuKYRrboHCNUThmVkg8yyhOs2hOlG5ZpbIPDS1JqbIPENUSbCLYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Seg Outs" = _t, Amount = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Seg Outs", Int64.Type}, {"Amount", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Seg Outs"}, {{"fn", fnRunningTotal, type table}}),
    Combined = Table.Combine(GroupedRows[fn])
in
    Combined

[PhilipTreacy · Accepted solution]

Hi @JenSme 

Download example Excel file

This works though it's a bit fiddly and requires maual coding in parts.  I'm sure I could do better if I rewrote my function but don't have time for that right now.  Can look at that later if you like.  Function code is based on this so you can give it a go yourself if you wish

Grouped Running Totals in Power Query

Here's the query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMFDSUTI1UIrVgfMMDVC4plCuKYRrboHCNUThmVkg8yyhOs2hOlG5ZpbIPDS1JqbIPENUSbCLYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Seg Outs" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Seg Outs", Int64.Type}, {"Amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Descending}}),
    BufferedValues = List.Buffer(#"Sorted Rows"[Amount]),
    BufferedGroup = List.Buffer(#"Sorted Rows"[Seg Outs]),

    RT = Table.FromColumns(
    {
      #"Sorted Rows"[Seg Outs], #"Sorted Rows"[Amount], #"Sorted Rows"[Index],
      fxGroupedRunningTotal(BufferedValues, BufferedGroup)
    },
    {
      "Seg Outs",
      "Amount",
      "Index",
      "Running Total"
    }),
    #"Sorted Rows1" = Table.Sort(RT,{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"})
in
    #"Removed Columns"

and the function to create the running total

let
    Source = (values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    ( 
        ()=> [ GRT = Number.From(values{0}), i = 0 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1} 
                 then [GRT = [GRT] + Number.From(values{[i] + 1}), i = [i] + 1]
                 else [GRT = Number.From(values{[i] + 1}), i = [i] + 1]
        
             otherwise [i = [i] + 1]
    ,
        each [GRT]
    )
in
    GRTList
in
    Source

Regards

Phil

[JenSme]

Thank you so much! This worked perfectly for what I was looking for!