Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
  • Jakinta's avatar
    Jakinta
    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

     

     

     

     

     

     

     

Jakinta's avatar
Jakinta
Solution Sage
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

 

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Jakinta 

     

    This is so brilliant! I still don't quite understand the logic, but it's brilliant! Thank you, Thank you! I've been working on it for days!