none
Split Rows by Condition RRS feed

  • Question

  • 

    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

    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

    Friday, November 30, 2018 5:08 PM

Answers

  • If I understand well what you want, the following code should work.
    Up to you to remove your original "months" column.

    The first SOURCE step is only replicating your data.
    You should only use the code starting at:

       Source = #"SOURCE",

    and replace #"SOURCE" by your table


    let SOURCE = let Source = #table( type table [start=date, End=date], { {#date(2014,1,1), #date(2015,1,1)}, {#date(2014,1,1), #date(2016,1,1)}, {#date(2014,1,1), #date(2017,1,1)}, {#date(2014,1,1), #date(2014,7,1)}, {#date(2014,1,1), #date(2016,1,31)}, {#date(2014,1,1), #date(2018,6,1)}, {#date(2014,1,1), #date(2015,3,1)} }), AddMonths = Table.AddColumn(#"Source", "months", each ((Date.Year([End])-Date.Year([start]))*12) + Date.Month([End]) - Date.Month([start]), Int64.Type), AddIndex = Table.AddIndexColumn(#"AddMonths", "Index", 1, 1), ReorderCol = Table.ReorderColumns(#"AddIndex", {"Index", "start", "End", "months"}) in #"ReorderCol", Source = #"SOURCE", AddNbOfYears = Table.AddColumn(#"Source", "Nb of Years", each Number.IntegerDivide([months],12), Int64.Type), AddNbOfRemainingMonths = Table.AddColumn(#"AddNbOfYears", "Remaining Months", each Number.Mod([months],12), Int64.Type), AddListOfRows = Table.AddColumn(#"AddNbOfRemainingMonths", "months splitted", (_)=> List.Combine({ List.Repeat({12}, (_)[Nb of Years]), if (_)[Remaining Months]=0 then {} else {(_)[Remaining Months]} }), type list ), ExpandListofRows = Table.ExpandListColumn(#"AddListOfRows", "months splitted"), RemCol = Table.RemoveColumns(#"ExpandListofRows", {"Nb of Years", "Remaining Months"}), ChType = Table.TransformColumnTypes(#"RemCol", {{"months splitted", Int64.Type}}) in #"ChType"




    Saturday, December 1, 2018 6:31 PM

All replies

  • If I understand well what you want, the following code should work.
    Up to you to remove your original "months" column.

    The first SOURCE step is only replicating your data.
    You should only use the code starting at:

       Source = #"SOURCE",

    and replace #"SOURCE" by your table


    let SOURCE = let Source = #table( type table [start=date, End=date], { {#date(2014,1,1), #date(2015,1,1)}, {#date(2014,1,1), #date(2016,1,1)}, {#date(2014,1,1), #date(2017,1,1)}, {#date(2014,1,1), #date(2014,7,1)}, {#date(2014,1,1), #date(2016,1,31)}, {#date(2014,1,1), #date(2018,6,1)}, {#date(2014,1,1), #date(2015,3,1)} }), AddMonths = Table.AddColumn(#"Source", "months", each ((Date.Year([End])-Date.Year([start]))*12) + Date.Month([End]) - Date.Month([start]), Int64.Type), AddIndex = Table.AddIndexColumn(#"AddMonths", "Index", 1, 1), ReorderCol = Table.ReorderColumns(#"AddIndex", {"Index", "start", "End", "months"}) in #"ReorderCol", Source = #"SOURCE", AddNbOfYears = Table.AddColumn(#"Source", "Nb of Years", each Number.IntegerDivide([months],12), Int64.Type), AddNbOfRemainingMonths = Table.AddColumn(#"AddNbOfYears", "Remaining Months", each Number.Mod([months],12), Int64.Type), AddListOfRows = Table.AddColumn(#"AddNbOfRemainingMonths", "months splitted", (_)=> List.Combine({ List.Repeat({12}, (_)[Nb of Years]), if (_)[Remaining Months]=0 then {} else {(_)[Remaining Months]} }), type list ), ExpandListofRows = Table.ExpandListColumn(#"AddListOfRows", "months splitted"), RemCol = Table.RemoveColumns(#"ExpandListofRows", {"Nb of Years", "Remaining Months"}), ChType = Table.TransformColumnTypes(#"RemCol", {{"months splitted", Int64.Type}}) in #"ChType"




    Saturday, December 1, 2018 6:31 PM
  • Hi Anthony,

    You overlooked the fact that for multiple years, all subsequent years starts from 02-Jan and not 01-Jan.

    Frankly, I didn't bother tackling this problem because I couldn't make sense of the requirements. For example, in the forth row of the source, how do you get 7 months between 01-Jan-2014 and 01-Jan-2015? How do you get 15 months between 01-Jan-2014 and 01-Mar-2015? In the output, why would you have duplicate rows for 12 months?

    Furthermore, there are only 11 months between 01-Feb and 31-Dec, for a total of 23 months between say, 01-Jan-2014 and 31-Dec-2016.
    Sunday, December 2, 2018 5:16 PM