Forum Discussion
Anonymous
5 years agoNot applicable
Create a row for every quarter
Hi everyone, I have a problem I hope someone can help me with I have a table that looks similar to the following: Project Number Monitoring Type Start of Project End of Project A Yearly ...
- 5 years ago
Hi,
Should be done neater, but until somebody posts it, this might serve...
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Project", type date}, {"End of Project", type date}}), Duration = Table.AddColumn(#"Changed Type", "Age", each if [Monitoring Type] = "Yearly" then List.Numbers(0,Number.Round(Duration.TotalDays([End of Project] - [Start of Project])/365,0),12) else List.Numbers(0,Number.Round(Duration.TotalDays([End of Project] - [Start of Project])/91.25,0),3)), Expanded = Table.ExpandListColumn(Duration, "Age"), MonitorStart = Table.AddColumn(Expanded, "Monitor Start", each Date.AddMonths([Start of Project], [Age])), MonitorEnd = Table.AddColumn(MonitorStart, "Monitor End", each if [Monitoring Type] = "Yearly" then Date.EndOfMonth(Date.AddMonths([Start of Project], [Age]+11)) else Date.EndOfMonth(Date.AddMonths([Start of Project], [Age]+2))), Final = Table.RemoveColumns(MonitorEnd,{"Age"}) in Finalā