Create a row for every quarter

[Anonymous]

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	April 1, 2020	March 31, 2022
B	Quarterly	April 1, 2020	March 31, 2021

I would like to have the table dynamically expanded to be the following:

Project	Monitoring Type	Project Start	Project End	Monitor Start	Monitor End
A	Yearly	April 1, 2020	March 31, 2022	April 1, 2020	March 31, 2021
A	Yearly	April 1, 2020	March 31, 2022	April 1, 2021	March 31, 2022
B	Quarterly	April 1, 2020	March 31, 2021	April 1, 2020	June 30, 2020
B	Quarterly	April 1, 2020	March 31, 2021	July 1, 2020	September 30, 2020
B	Quarterly	April 1, 2020	March 31, 2021	October 1, 2020	December 31, 2020
B	Quarterly	April 1, 2020	March 31, 2021	January 1, 2021	March 31, 2021

Basically what I'm trying to achieve is each row of the table I put in have rows added for the type of monitoring period based on the start and end of the project.

I don't know if it's possible, but any help would be appreciated.

Thanks in advance,

Vanlang

[edhans]

See if this works for you @Anonymous - it is a little more complex than what @Jakinta did, but I'm using the date functions here to add years or quarters, so it would handle leap years automatically.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYpMTSzKqQQyHAuKMnMUDHUUjAyMDIB838Si5AwFY4iAkVKsTrSSE1A4sDSxqCSVCC2GSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Number" = _t, #"Monitoring Type" = _t, #"Start of Project" = _t, #"End of Project" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Project", type date}, {"End of Project", type date}}),
    Year = Table.SelectRows(#"Changed Type", each [Monitoring Type] = "Yearly"),
    YearDateRange = 
        List.Generate(
            () => [x = Year[Start of Project]{0}],
            each [x] < Year[End of Project]{0},
            each [x = Date.AddYears([x], 1)],
            each [x]
        ),
    Quarter = Table.SelectRows(#"Changed Type", each [Monitoring Type] = "Quarterly"),
    QuarterDateRange = 
        List.Generate(
            () => [x = Quarter[Start of Project]{0}],
            each [x] < Quarter[End of Project]{0},
            each [x = Date.AddQuarters([x], 1)],
            each [x]
        ),
    NewDates = 
        Table.AddColumn(#"Changed Type", "Monitor Start", each if [Monitoring Type] = "Yearly" then YearDateRange
                else QuarterDateRange),
    #"Expanded Range" = Table.ExpandListColumn(NewDates, "Monitor Start"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Range",{{"Monitor Start", type date}}),
    #"Added Monitor End" = 
        Table.AddColumn(
            #"Changed Type1", 
            "Monitor End", 
            each if [Monitoring Type] = "Yearly" then Date.AddDays(Date.AddYears([Monitor Start], 1), -1)
                else  Date.AddDays(Date.AddQuarters([Monitor Start], 1), -1),
            Date.Type
        )
in
    #"Added Monitor End"

There are two lists generated, one YearDateRange and QuarterDateRange. The quarter list looks like this:

It starts with the first date, then keeps adding quarters until it no longer less than your ending date.

The year list works the same way, but adds years.

Then I added a final Montitor End column which is simply one more year/quarter minus one day.

End result:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

This is so brilliant @edhans Thank you so very much!

[edhans]

Glad I was able to help @Anonymous 

[Jakinta · Accepted solution]

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]

@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!