Power Query Running Totals For Each Subsequent Months

[legalhustler]

Hello,

I am using Power Query for Excel and wanted a dynamic running total for each month shown on the far right. The source data comes from another system so after the end of December it automatically shows January, February, March etc. For each row starting with Oct as the first month I want the cumulative running total for the current month + the previous month values.

So for the first row Oct should show $10, Nov $30 (10+20) and Dec $60 (10+20+30). The second row should show $5, $15 (5+10), and $30 (5+10+15) for Oct, Nov, Dec, respectively.

When January data shows I want it to dynamically add the values for Oct+Nov+Dec. I'm hoping the solution is efficient so it doesn't slow down my query. Thanks in advance

Region	Station	ID	Type	Oct	Nov	Dec
North	111	0000	Low	$10	$20	$30
South	222	9999	Low	$5	$10	$15
East	333	8888	Medium	$20	$40	$60
West	444	7777	High	$3	$6	$9

 

[JGordon11]

This code replaces the month columns with the running totals from the first month column found formatted as "mmm". Is that what you are asking for?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcc  =Table.ColumnCount(Source),
    tcn = Table.ColumnNames(Source),
    MonthNameList = List.Generate(()=>1,(x)=> x<13,(x)=> x+1, (x)=> Text.Start(Date.MonthName(#date(2022,x,1)), 3)),
    numLeadCols = List.PositionOfAny(tcn, MonthNameList),
    tblAddListCol = Table.AddColumn(Source,"Running", each List.Generate(()=>1,(x)=>x<=tcc-numLeadCols, (x)=> x+1, (x)=> List.Sum(List.Range(Record.ToList(_),numLeadCols,x)))),
    monthlist = List.Skip(tcn,numLeadCols),
    tblDeleteCols = Table.RemoveColumns(tblAddListCol,monthlist),
    Extract = Table.TransformColumns(tblDeleteCols, {"Running", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Running", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), monthlist),
    MonthTypeList = List.Transform(monthlist, each {_, Currency.Type}),
    ChgType = Table.TransformColumnTypes(Split,MonthTypeList)
in
    ChgType

 

[Bo_Ry]

Maybe try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = List.Skip(Table.ColumnNames(Source),4),
    Arec = Table.AddColumn(Source, "M", each Record.FromList(List.Skip(List.Accumulate(List.Skip(Record.ToList(_),4),{0},(s,l)=> s&{List.Last(s)+l } ),1),Month)),
    Expanded = Table.ExpandRecordColumn(Table.RemoveColumns(Arec,Month), "M", Month)
in
    Expanded

 

[dpiper]

Can you please post the data set used for the above m codes?

 

[Fluff]

It's in the OP's post.

 

[dpiper]

Can you please post the data set used for the above m codes?

also what if rolling should go from Jan-Dec and start counting again if year changes?

 

[Fluff]

If you have an actual question, you are better of starting a new thread & posting your data.