# Split Rows by Condition

• ### 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

• 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)}
}),
ReorderCol = Table.ReorderColumns(#"AddIndex", {"Index", "start", "End", "months"})
in #"ReorderCol",

Source = #"SOURCE",
List.Combine({
List.Repeat({12}, (_)[Nb of Years]),
if (_)[Remaining Months]=0 then {} else {(_)[Remaining Months]}
}),
type list
),
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)}
}),
ReorderCol = Table.ReorderColumns(#"AddIndex", {"Index", "start", "End", "months"})
in #"ReorderCol",

Source = #"SOURCE",
List.Combine({
List.Repeat({12}, (_)[Nb of Years]),
if (_)[Remaining Months]=0 then {} else {(_)[Remaining Months]}
}),
type list
),
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