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?

start	end	months
1/1/2014	1/1/2015	12
1/1/2014	1/1/2016	24
1/1/2014	1/1/2017	36
1/1/2014	1/1/2015	7
1/1/2014	1/3/2015	15

<tbody>
</tbody>

start	end	months
1/1/2014	1/1/2015	12
1/1/2014	1/1/2015	12
1/1/2014	1/1/2015	24
2/1/2015	31/1/2016	24
1/1/2014	1/1/2015	36
2/1/2015	1/1/2016	36
2/1/2016	31/1/2017	36
1/1/2014	1/1/2015	7
1/1/2014	1/1/2015	15
2/1/2015	1/3/2015	15

<tbody>
</tbody>

 

[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.

Start Date	End Date	Contract Length	Status	Descriptions
1/1/2018	31/12/2018	12 Months	Before
1/1/2018	31/12/2018	12 Months	Final Result	12 Months Block
1/1/2018	31/12/2018	12 Months	Final Result	12 Months Block
1/1/2018	28/02/2023	50 Months	Before
1/1/2018	31/12/2018	50 Months	Final Result	12 Months Block
1/1/2019	31/12/2019	50 Months	Final Result	12 Months Block
1/1/2020	31/12/2020	50 Months	Final Result	12 Months Block
1/1/2021	31/12/2021	50 Months	Final Result	12 Months Block
1/1/2022	31/12/2022	50 Months	Final Result	12 Months Block
1/1/2023	28/02/2023	50 Months	Final Result	2 Months Block

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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

start	end	months
01/01/2014	31/12/2014	12
01/01/2014	31/12/2015	24
01/01/2014	31/12/2016	36
01/01/2014	31/12/2014	7
01/01/2014	01/03/2015	15
01/01/2018	31/12/2018	12
01/01/2018	28/02/2023	50

<tbody>
</tbody>

Output

Year Start	End	months
01/01/2014	31/12/2014	12
01/01/2014	31/12/2014	24
01/01/2015	31/12/2015	24
01/01/2014	31/12/2014	36
01/01/2015	31/12/2015	36
01/01/2016	31/12/2016	36
01/01/2014	31/12/2014	7
01/01/2014	31/12/2014	15
01/01/2015	01/03/2015	15
01/01/2018	31/12/2018	12
01/01/2018	31/12/2018	50
01/01/2019	31/12/2019	50
01/01/2020	31/12/2020	50
01/01/2021	31/12/2021	50
01/01/2022	31/12/2022	50
01/01/2023	28/02/2023	50

<tbody>
</tbody>

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