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

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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.