cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vanlang
Frequent Visitor

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
Responsive Resident
Responsive Resident

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
vanlang
Frequent Visitor

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
Responsive Resident
Responsive Resident

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

vanlang
Frequent Visitor

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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors