Running totals, sort by date in Power Editor

[Anonymous]

Hi All,

it s 1am and I m boiled.. so if I m asking you something stupid.. sorry in advance.

I m trying to solve this by date

I wrote this code.. (that I m so proud) ... but it is not sorting by Month for Enroll Calc...

#"Grouped Rows" = Table.Group(#"Removed Columns13", {"Sigeca 4digit"}, {{"Count", each _, type table [Sigeca 4digit=nullable text, Month for Enroll Calc=nullable date, TE NET=nullable number]}}),

//Function to calculate running totals

RunFunction = (RunTable as table) as table=>

let
#"Added Index" = Table.AddIndexColumn(RunTable, "Index", 1, 1, Int64.Type),
#"Added Custom1000" = Table.AddColumn(#"Added Index", "CustomIndex", each List.Sum( List.Range (#"Added Index"[TE NET],0, [Index]))),
#"Changed Type1000" = Table.TransformColumnTypes(#"Added Custom1000",{{"CustomIndex", type number}})
in
#"Changed Type1000",

// Call the function

RunTotals = Table.TransformColumns(#"Grouped Rows",{"Count", each RunFunction(_)}),
#"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"Month for Enroll Calc", "TE NET", "CustomIndex"}, {"Month for Enroll Calc", "TE NET", "CustomIndex"}),
#"Changed Type27" = Table.TransformColumnTypes(#"Expanded Count",{{"CustomIndex", type number}})
in
#"Changed Type27"

but the result is wrong.. it should start from sept 2022.. 

please help me!! I m super desperate.

thanks in advance.

Simone

[Anonymous · Accepted solution]

I solved adding this #"Added Index" = Table.Buffer(Table.AddIndexColumn(Table.Sort(RunTable,{{"Month for Enroll Calc", Order.Ascending}}), "Index",1,1, Int64.Type)),

below the full code if anyone needs.

/Function to calculate running totals

RunFunction = (RunTable as table) as table=>

let
#"Added Index" = Table.Buffer(Table.AddIndexColumn(Table.Sort(RunTable,{{"Month for Enroll Calc", Order.Ascending}}), "Index",1,1, Int64.Type)),
#"Added Custom1000" = Table.AddColumn(#"Added Index", "CustomIndex", each List.Sum( List.Range (#"Added Index"[TE NET],0, [Index]))),
#"Changed Type1000" = Table.TransformColumnTypes(#"Added Custom1000",{{"CustomIndex", type number}})
in
#"Changed Type1000",

// Call the function

RunTotals = Table.TransformColumns(#"Grouped Rows",{"Count", each RunFunction(_)}),
#"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"Month for Enroll Calc", "TE NET", "CustomIndex"}, {"Month for Enroll Calc", "TE NET", "CustomIndex"}),
#"Changed Type27" = Table.TransformColumnTypes(#"Expanded Count",{{"CustomIndex", type number}}),
#"Removed Columns14" = Table.RemoveColumns(#"Changed Type27",{"TE NET"}),
#"Changed Type28" = Table.TransformColumnTypes(#"Removed Columns14",{{"Month for Enroll Calc", type date}}),
#"Renamed Columns14" = Table.RenameColumns(#"Changed Type28",{{"CustomIndex", "Accumulated Attrition"}})
in
#"Renamed Columns14"

[amitchandak]

@Anonymous , refer if these can help

https://goodly.co.in/running-total-power-query/

https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query