cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vanlang
Helper I
Helper I

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 NumberMonitoring TypeStart of ProjectEnd of Project
AYearlyApril 1, 2020March 31, 2022
BQuarterlyApril 1, 2020March 31, 2021

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

ProjectMonitoring TypeProject StartProject EndMonitor StartMonitor End
AYearlyApril 1, 2020March 31, 2022April 1, 2020March 31, 2021
AYearlyApril 1, 2020March 31, 2022April 1, 2021March 31, 2022
BQuarterlyApril 1, 2020March 31, 2021April 1, 2020June 30, 2020
BQuarterlyApril 1, 2020March 31, 2021July 1, 2020September 30, 2020
BQuarterlyApril 1, 2020March 31, 2021October 1, 2020December 31, 2020
BQuarterlyApril 1, 2020March 31, 2021January 1, 2021March 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

1 ACCEPTED SOLUTION
Jakinta
Solution Sage
Solution Sage

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_0-1619656038637.png

 

 

 

 

View solution in original post

5 REPLIES 5
edhans
Super User III
Super User III

See if this works for you @vanlang - 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:

edhans_0-1619664097130.png

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:

edhans_1-1619664186605.png

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.

 

 


 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Glad I was able to help @vanlang 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Jakinta
Solution Sage
Solution Sage

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_0-1619656038637.png

 

 

 

 

View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors