Forum Discussion
Running Total with Function failled
Hi
I'm having the following table and want to make a running total with a reset on every year.
Therefore I'm grouping the data on year and org.units
let
Quelle = Excel.CurrentWorkbook(){[Name="SAPCrosstab_Vol_Mon"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Date", type datetime}, {"Purchasing org.", Int64.Type}, {"Calendar Year", Int64.Type}, {"Calendar Month", Int64.Type}, {"Anzahl", Int64.Type}, {"SumMon", type number}, {"ID", Int64.Type}}),
#"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ", {"Purchasing org.", "Calendar Year"}, {{"Tabelle", each _, type table [Date=nullable datetime, #"Purchasing org."=nullable number, Calendar Year=nullable number, Calendar Month=nullable number, Anzahl=nullable number, SumMon=nullable number, ID=nullable number]}}),
RunFunction = (RunTable as table) as table =>
let
#"Calc RT" = Table.AddColumn(RunTable, "RT", each List.Sum(List.FirstN(#"Gruppierte Zeilen"[SumMon],#"Gruppierte Zeilen"[Calendar Month])))
in
#"Calc RT",
RunTotals = Table.TransformColumns( #"Gruppierte Zeilen",{"Tabelle", each RunFunction(_)}),
#"Erweiterte Tabelle" = Table.ExpandTableColumn(RunTotals, "Tabelle", {"Date", "Calendar Month", "Anzahl", "SumMon", "ID", "RT"}, {"Tabelle.Date", "Tabelle.Calendar Month", "Tabelle.Anzahl", "Tabelle.SumMon", "Tabelle.ID", "Tabelle.RT"})
in
#"Erweiterte Tabelle"
The function is adding the running total column
But the "SumMon" is not known:
Do I have to adress my data column in a different way?
RunFunction = (RunTable as table) as table =>
let
#"Calc RT" = Table.AddColumn(RunTable, "RT",
each List.Sum(List.FirstN(#"Gruppierte Zeilen"[SumMon],#"Gruppierte Zeilen"[Calendar Month])))
in
#"Calc RT",
Once this is done I guess Calender Monaht will fail as well.
many thx
hari