Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Split Rows based on Days

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
IDBeginEndDays
A8/15/20188/13/20211094
B3/13/201810/18/2019584
C9/1/20188/31/2019364
D11/1/20187/31/2019272
    
    
FINAL DATA
IDBeginEndDays
A8/15/20188/14/2019364
A8/15/20198/13/2020364
A8/14/20208/13/2021364
B3/13/20183/12/2019364
B3/13/201910/18/2019220
C9/1/20188/31/2019364
D11/1/20187/31/2019272
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

12 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        parry2k
        Super User
        6 years ago

        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