Forum Discussion
Create a row for every quarter
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
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.