Forum Discussion
Expand Dates with Start and End Date & Create Running Total inside those Expanded Group Dates
think you can get the result with one step, try this code in your new step
=Table.FromRecords(List.TransformMany(Table.ToRecords(PreviousStepName),each {0..Number.From([End Date])-Number.From([Start Date])},(x,y)=>x&[Year=Date.AddYears(x[Start Date],y),RunningTotal=x[Implementation Cost]+x[Operational Savings Cost]*y]))
So it didnt quite work out they way it supposed to it essentally all of the Categories the same along with the Implementation Costs and Operational Savings Costs and instead of each Category stopping at 2050 it just keeps going.
Do i add your code after my Previous Expanded Dates code or get rid of that and use what you have?
//My Previous Steps
AllDates = {Number.From([Start Date])..Number.From([End Date])},
StartofMonthDates =
List.Distinct(List.Transform(AllDates, each Date.StartOfYear(Date.From(_))))
in
StartofMonthDates),
//Your Code
#"Expanded Dates1" = Table.ExpandListColumn(#"Added Custom4", "Dates"),
Custom1 = Table.FromRecords(List.TransformMany(Table.ToRecords(#"Expanded Dates1"),each {0..Number.From([End Date])-Number.From([Start Date])},(x,y)=>x&[Year=Date.AddYears(x[Start Date],y),RunningTotal=x[Estimated Implementation Cost]+x[Operational Cost Saving]*y]))
in
Custom1
I isolated one category because it should look something like this and then repeat for every category. The Custom column is
= Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then [Estimated Implementation Cost] else [Operational Cost Saving]) and then i use the index to create a running total.
Then repeat for each category
Much Appreciated