Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGlad I was able to help @Anonymous
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |