Split Rows based on Days

[Anonymous]

Hello Community,

I want to split rows based on days.Please see below Row Data and Final Data for sample.

If Days =<364 Days then not split just keep as it is but if Days>364 then split every row by 364 Days.

Example:

If Days are 1094 then split 3 rows for 364 Days & 364 Days & 364 Days.

if Days are 584 then split rows into 2,first rows is for 364 Days and second rows for 220.

ROW DATA
ID	Begin	End	Days
A	8/15/2018	8/13/2021	1094
B	3/13/2018	10/18/2019	584
C	9/1/2018	8/31/2019	364
D	11/1/2018	7/31/2019	272
FINAL DATA
ID	Begin	End	Days
A	8/15/2018	8/14/2019	364
A	8/15/2019	8/13/2020	364
A	8/14/2020	8/13/2021	364
B	3/13/2018	3/12/2019	364
B	3/13/2019	10/18/2019	220
C	9/1/2018	8/31/2019	364
D	11/1/2018	7/31/2019	272

[parry2k]

@Anonymous seems like there is mistake in calculation, if you look at A, 1094 days divided by 3 times x 364 make it is 1092 and I believe there should be fourth row with 2 days, isn't it?

[Anonymous]

Hi Perry,

You can think in that way,

Days=1094 is 36 Months

Days=584 is 19  Months

Days=364 is 12 Months

Days=272 is 9 Months

[parry2k]

@Anonymous just checking the days values are always going to be on of these options:

1094

584

364

272

Reason is to understand what should be the best logic to achieve it, if these are the only four options it will be a different logic 

[Anonymous]

As per my understadning,

Example:if Days are <364 (12 Months or Less) then keep only  one rows,
if Days>364 then split rows according to days,it means if Days are 584 (19 Months:1 row for 12 months and 2 row for 7 month) then split first row for 364 Days and Second row should be (584-364).if days are 1094 then first row should be 364,second rows should be 364 and thrid row should be 1094-(364+364)=366.

Let me know if you need more clarifications.

[Ashish_Mathur]

Hi,

Your logic is inconsistent.  For 1094 rows, there should be 4 rows (the last row showing 2 days).  How many rows would you expect if there were 1200 days?

[Anonymous]

Hi Ashish,
Below screenshot you are looking , I have copied from excel to power bi  and result is correct.Suppose ID=A has 36 Months contract or 1094 Days. Begin column and End column resule should match and i think that's why we have 364 Days.

Can you suggest me how to split 1094 days in 4 rows split?

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Begin", type date}, {"End", type date}, {"Days", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Remainder", each Number.Mod([Days],364)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Integer", each Number.IntegerDivide([Days],364)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rows to be created", each if [Integer]=0 then 1 else if [Remainder]=0 then [Integer] else [Integer]+1),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each {Number.From(1)..Number.From([Rows to be created])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"),
    #"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Number of days", each if [Days]<=364 then [Days] else if [Custom]*364<=[Days] then 364 else [Remainder]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Days", "Remainder", "Integer", "Rows to be created", "Custom"})
in
    #"Removed Columns"

Hope this helps.

[WojtekP]

I tried to split total contract value (TCV) into annual contract value (ACV) but with no luck.

My data has a mixture of contracts duration between 1 day to 5years+

All contracts =< 1 year require no attention but if a contract is eg 4 year long, it should be split into 4 rows as follows

Source:

Index	Amount	Start Date	End Date	Year	Days
1	261,600.00	01/06/2016	31/12/2019	2016	1308

Result:

Index	Amount	Start Date	End Date	Year	Days
1	72,800.00	01/06/2016	31/05/2017	2016	364
1	72,800.00	01/06/2017	31/05/2018	2017	364
1	72,800.00	01/06/2018	31/05/2019	2018	364
1	42,600.00	01/06/2019	31/12/2019	2019	213

This is what I am trying to achieve. In this example Im getting 1308 days (31/12/2019 - 01/06/2016) but when i split into 4 years somehow i am getting 1305 but that is another matter.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Begin", type date}, {"End", type date}, {"Days", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Remainder", each Number.Mod([Days],364)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Integer", each Number.IntegerDivide([Days],364)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rows to be created", each if [Integer]=0 then 1 else if [Remainder]=0 then [Integer] else [Integer]+1),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each {Number.From(1)..Number.From([Rows to be created])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"),
    #"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Number of days", each if [Days]<=364 then [Days] else if [Custom]*364<=[Days] then 364 else [Remainder]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.1", each if [Number of days]=364 then Date.AddDays(Date.AddYears([Begin],[Custom]),-1) else [End]),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Custom.2", each Date.AddYears([Begin],[Custom]-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom6",{"Days", "Remainder", "Integer", "Rows to be created", "Custom", "End","Begin"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Custom.2", "Custom.1", "Number of days"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Begin"}, {"Custom.1", "End"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Begin", type date}, {"End", type date}})
in
    #"Changed Type1"

Hope this helps.

[Ashish_Mathur]

Hi,

Your question is not clear and neither is the data that you have shared.  Share data in a format that can be pasted in an MS Excel file, desribe the business question and show the expected result.  

[WojtekP]

My previous post is now edited.

[Anonymous]

Hi Perry,

Thank you so much for your comments.

As per the requirements it should go by 364 Days +364 Days+366 Days and require only 3 rows (36 Months).

Thanks