Forum Discussion
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 | 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
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
5 Replies
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 FinalThis 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!
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.
