Split Rows by Condition

[tejapowerbi]

Happy Friday Everyone,
I have a below scenarios where I want to split rows (not column) based on condition. If the Month column has <12 then only rows required but if it is 12 month then 2 Rows required,15 month then one row with 12 months and one row with 3 months and if it is 24 months then split two rows in 12 months and 36 months then split 3 rows in 12 months and so on..Can any provide any suggestion?

[TABLE="width: 225"]
<tbody>[TR]
[TD]start
[/TD]
[TD]end
[/TD]
[TD]months
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2016
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2017
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/3/2015
[/TD]
[TD]15
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 225"]
<tbody>[TR]
[TD]start
[/TD]
[TD]end
[/TD]
[TD]months
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]2/1/2015
[/TD]
[TD]31/1/2016
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]2/1/2015
[/TD]
[TD]1/1/2016
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]2/1/2016
[/TD]
[TD]31/1/2017
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]1/1/2014
[/TD]
[TD]1/1/2015
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]2/1/2015
[/TD]
[TD]1/3/2015
[/TD]
[TD]15
[/TD]
[/TR]
</tbody>[/TABLE]

 

[peter789]

Can you explain the rule for calculating the end dates please? In particular why some are on the 31st of the month. I can see a fairly simple method using List.Dates but there a couple of problems both with the end date logic and handling Leap years.

 

[tejapowerbi]

Hi Peter,
thank you so much for attending my question.
i am really sorry it was a typo.
What I want is if start date is 1/1/2014 then end date should be 31/12/2014 (365 Days) and next period start at 1/1/2015.if I put my more details I can say that,
If months are under 12 I want to split in one row and for 12 months I want to split in 2 rows and for 17 months I want to split 1 row for 12 months and one for 5 months, if the months are 50 then 4 rows with 12 months and 5 rows with 2 months. Let me know if you need more information.
Once again thank you so much.
tejas

 

[peter789]

Tejas,
I'm still a bit confused. Can you repost the example tables with the correct values please? The 12 month and 50 month cases are not clear. For 12 months what dates do you want in the second row, for 50 months do you really want 9 rows?
Peter

 

[tejapowerbi]

Peter,
i have go through list dates options and my contract months are keep changing so i am not sure how to use.Please check the below table.let me know if you need more information.
[TABLE="width: 591"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date [/TD]
[TD]Contract Length[/TD]
[TD]Status[/TD]
[TD]Descriptions[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]31/12/2018[/TD]
[TD]12 Months[/TD]
[TD]Before[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]31/12/2018[/TD]
[TD]12 Months[/TD]
[TD]Final Result[/TD]
[TD]12 Months Block[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]31/12/2018[/TD]
[TD]12 Months[/TD]
[TD]Final Result[/TD]
[TD]12 Months Block[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]28/02/2023[/TD]
[TD]50 Months[/TD]
[TD]Before[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]31/12/2018[/TD]
[TD]50 Months[/TD]
[TD]Final Result[/TD]
[TD]12 Months Block[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]31/12/2019[/TD]
[TD]50 Months[/TD]
[TD]Final Result[/TD]
[TD]12 Months Block[/TD]
[/TR]
[TR]
[TD]1/1/2020[/TD]
[TD]31/12/2020[/TD]
[TD]50 Months[/TD]
[TD]Final Result[/TD]
[TD]12 Months Block[/TD]
[/TR]
[TR]
[TD]1/1/2021[/TD]
[TD]31/12/2021[/TD]
[TD]50 Months[/TD]
[TD]Final Result[/TD]
[TD]12 Months Block[/TD]
[/TR]
[TR]
[TD]1/1/2022[/TD]
[TD]31/12/2022[/TD]
[TD]50 Months[/TD]
[TD]Final Result[/TD]
[TD]12 Months Block[/TD]
[/TR]
[TR]
[TD]1/1/2023[/TD]
[TD]28/02/2023[/TD]
[TD]50 Months[/TD]
[TD]Final Result[/TD]
[TD]2 Months Block[/TD]
[/TR]
</tbody>[/TABLE]

 

[peter789]

I'm sorry it has taken so long but I've got as near as I can. Is this roughly what you are after?

Source Table

[TABLE="width: 219"]
<tbody>[TR]
[TD]start[/TD]
[TD]end[/TD]
[TD]months[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2014[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2016[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2014[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]01/03/2015[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]28/02/2023[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]


Output
[TABLE="width: 229"]
<tbody>[TR]
[TD]Year Start[/TD]
[TD]End[/TD]
[TD]months[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2014[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2014[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2015[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2014[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2015[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]31/12/2016[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2014[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]31/12/2014[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2015[/TD]
[TD="align: right"]01/03/2015[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"]31/12/2019[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2020[/TD]
[TD="align: right"]31/12/2020[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2021[/TD]
[TD="align: right"]31/12/2021[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2022[/TD]
[TD="align: right"]31/12/2022[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2023[/TD]
[TD="align: right"]28/02/2023[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

I can't work out the 12 month case with the extra row; is it necessary, the logic doesn't seem to be consistent?
If it looks OK I can post the code

Peter

 

[tejapowerbi]

Hi Peter,thank you so much.that would be great. It works for me perfectly fine.please provide the cod.thanks

 

[peter789]

Alright here goes. I should add I am not a real M expert so what you see is the attempts of an enthusiastic learner! I have left the code a bit ugly so you can see the steps as generated by the GUI.

let
Source = Excel.CurrentWorkbook(){[Name="TableDates"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}, {"months", Int64.Type}}),
//This is the complicated part. Modified from various Internet Blogs. List.Generate can behave in a similar manner to nested Loops

AddedYearDates =
Table.AddColumn(
Source,
"Year Start",
(fnAddYear) => List.Generate(
() => [YearDate = fnAddYear[start], Counter = 1],
each [YearDate] < fnAddYear[end],
each [YearDate = Date.AddYears(fnAddYear[start],[Counter]),
Counter = [Counter] + 1],
each [YearDate]),
type {date} ),

ExpandedYearDates = Table.ExpandListColumn(AddedYearDates , "Year Start" ),
#"Changed Type1" = Table.TransformColumnTypes(ExpandedYearDates,{{"start", type date}, {"end", type date}, {"Year Start", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"start", "Year Start", "end", "months"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"start"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Date.AddYears([Year Start],1)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each Date.AddDays([Custom],-1)),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1] <= [end] then [Custom.1] else [end]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Custom", "Custom.1"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"months", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Custom.2", "End"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns",{{"End", type date}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type4",{"Year Start", "end", "End", "months"}),
Result = Table.RemoveColumns(#"Reordered Columns1",{"end"})
in
Result

Peter

 

[tejapowerbi]

Thank you so much Peter,i am totally new to Power BI and thank you so much for your time help.I am going to use it and let you know how it is behaving.Can you please provide your email? My email is teja.k@1919@gmail.com thanks

 

[tejapowerbi]

Sorry teja.k1919@gmail.com