cancel
Showing results for
Did you mean:
Regular Visitor

## Split Rows based on Condition

Happy Monday Community Members,

I have a below scenarios for your suggestions. I have used list.dates functions but it does not generate correct result because my Contract Months keep changing.

My requirements is that if contract months is >=12 Months then I want to crate  2 Rows with 12 Months Block and if contract is <12 then create one row.let me know if you need more information.Thanks

 Start Date End Date Contract Length 1/1/2015 31/12/2015 12 Months 1/1/2015 31/07/2015 7 Months 1/1/2015 31/07/2016 19 Months 1/1/2015 31/12/2016 24 Months 1/1/2015 31/07/2018 43 Months Start Date End Date Contract Length Row Split Comments 1/1/2015 31/12/2015 12 Months for 12 Months 2 row split 1/1/2015 31/12/2015 12 Months for 12 Months 2 row split 1/1/2015 31/07/2015 7 Months for 7 Months 1 Row Split 1/1/2015 31/12/2015 19 Months for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split 1/1/2016 31/07/2016 19 Months for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split 1/1/2015 31/12/2015 24 Months for 24 Months 2 row split of 12 Months 1/1/2016 31/12/2016 24 Months for 24 Months 2 row split of 12 Months 1/1/2015 31/12/2015 43 Months for 43 Months 3 row split of 12 Months and 1 Row with 7 Months 1/1/2016 31/12/2016 43 Months for 43 Months 3 row split of 12 Months and 1 Row with 7 Months 1/1/2017 31/12/2017 43 Months for 43 Months 3 row split of 12 Months and 1 Row with 7 Months 1/1/2018 31/07/2018 43 Months for 43 Months 3 row split of 12 Months and 1 Row with 7 Months
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Split Rows based on Condition

Hi,

Try this code

```let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}, {"Contract Length", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Contract Length", "Contract Length - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Start Date", "End Date", "Contract Length", "Contract Length - Copy"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Contract Length - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length - Copy.1", "Contract Length - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length - Copy.1", Int64.Type}, {"Contract Length - Copy.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Contract Length - Copy.2"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Number.Mod([#"Contract Length - Copy.1"],12) <> 0 then Number.IntegerDivide([#"Contract Length - Copy.1"], 12)+1 else if [#"Contract Length - Copy.1"]=12 then 2 else Number.IntegerDivide([#"Contract Length - Copy.1"], 12)),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom", "Custom.1"}),
Partition = Table.Group(#"Removed Columns1", {"Index"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}, {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Partition", "Advance start date by", each if [#"Contract Length - Copy.1"] < 12 then 0 else 12 *([Index1]-1)),

#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom3",{"Index", "Start Date", "Custom", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by"}),
#"Added Custom4" = Table.AddColumn(#"Reordered Columns1", "Advance end date by", each if [#"Contract Length - Copy.1"]-[Advance start date by] > 12 then 12 else [#"Contract Length - Copy.1"]-[Advance start date by]),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom5",{"Index", "Start Date", "Custom", "Custom.1", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Start Date", "End Date", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Start Date"}, {"Custom.1", "End Date"}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Start Date", type date}, {"End Date", type date}}, "en-IN"),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type with Locale",{"Index"})
in
#"Removed Columns3"```

14 REPLIES 14
Member

## Re: Split Rows based on Condition

• duplicate your table (so we got table1 and table2)
• in the table2, duplicate the column Contract Length (Contract Length - Copy)
• in this new column Contract Length - Copy, you replace the value  Months by nothing (to keep only the numbers)
• change the format of your column (Text to Whole Number)
• select only the number granter than or equal to 12

• Remove the column Contract Length - Copy because you don't need it anymore
• Use the function Append Queries as New to create a new table made of the table1 and table2

This new table entitled Append1 is what you need if I'm not wrong.

Regards,

CR

Regular Visitor

## Re: Split Rows based on Condition

Hi CR,

Thank you so much for your suggestion.

Questions 1:What is the do with contract length has <12 ?

Question 2: How is it going to split the column by contract lengths?

Can you please explain me in details?

Thanks

Member

## Re: Split Rows based on Condition

Regarding the ones <12, I just kept the original line because, in the table2, I kept only the line >= 12.

Table1: data <12 + data >=12

Table2: date >=12

Append1 = merge of Table1 + Table2 = data <12 + data >=12 + Data < 12 = 2(Data >=12) + Data <12

It refers to your requirement if contract months is >=12 Months then I want to create  2 Rows with 12 Months Block and if contract is <12 then create one row

Concerning the split you are talking about, I'mreally sorry, I just understood what you need ! Well, I'll try to reply tomorrow because you need few steps to get that (not so simple in my opinion) but probably somebody will complete.

Regards,

CR

Highlighted
Regular Visitor

Hi CR,

Super User

## Re: Split Rows based on Condition

Hi,

This M code works

let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}, {"Contract Length", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Contract Length", "Contract Length - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Contract Length - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length - Copy.1", "Contract Length - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length - Copy.1", Int64.Type}, {"Contract Length - Copy.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Contract Length - Copy.2"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Number.Mod([#"Contract Length - Copy.1"],12) <> 0 then Number.IntegerDivide([#"Contract Length - Copy.1"], 12)+1 else Number.IntegerDivide([#"Contract Length - Copy.1"], 12)),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Contract Length - Copy.1", "Custom", "Custom.1"})
in
#"Removed Columns1"

Regular Visitor

## Re: Split Rows based on Condition

Hi Ashish,

Than you so much for your replay.

Still the result is not showing correct,start data and end date is not showing right

For 12 Months ,

Source without Transformation

 Start Date End Date Contract Length 1/1/2015 31/12/2015 12 Months

Final Result

 Start Date End Date Contract Length Row Split Comments 1/1/2015 31/12/2015 12 Months for 12 Months 2 row split 1/1/2015 31/12/2015 12 Months for 12 Months 2 row split

For 19 Months,

Source without Transformation

 Start Date End Date Contract Length 1/1/2015 31/07/2016 19 Months

Final Result

 1/1/2015 31/12/2015 19 Months for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split 1/1/2016 31/07/2016 19 Months for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split

Thanks

Regular Visitor

## Re: Split Rows based on Condition

Hello,

i have tried below code  but it did not generate correct result for 12 Months.Does anyone has any suggestions?

let
Source = Excel.CurrentWorkbook(){[Name="TableDates"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}, {"months", Int64.Type}}),

Source,
"Year Start",
() => [YearDate = fnAddYear[start], Counter = 1],
Counter = [Counter] + 1],
each [YearDate]),
type {date} ),

ExpandedYearDates = Table.ExpandListColumn(AddedYearDates , "Year Start" ),
#"Changed Type1" = Table.TransformColumnTypes(ExpandedYearDates,{{"start", type date}, {"end", type date}, {"Year Start", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"start", "Year Start", "end", "months"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"start"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Custom", "Custom.1"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"months", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Custom.2", "End"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns",{{"End", type date}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type4",{"Year Start", "end", "End", "months"}),
Result = Table.RemoveColumns(#"Reordered Columns1",{"end"})

Super User

## Re: Split Rows based on Condition

Hi,

This M code works

let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}, {"Contract Length", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Contract Length", "Contract Length - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Start Date", "End Date", "Contract Length", "Contract Length - Copy"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Contract Length - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length - Copy.1", "Contract Length - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length - Copy.1", Int64.Type}, {"Contract Length - Copy.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Contract Length - Copy.2"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Number.Mod([#"Contract Length - Copy.1"],12) <> 0 then Number.IntegerDivide([#"Contract Length - Copy.1"], 12)+1 else if [#"Contract Length - Copy.1"]=12 then 2 else Number.IntegerDivide([#"Contract Length - Copy.1"], 12)),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom", "Custom.1"}),
Partition = Table.Group(#"Removed Columns1", {"Index"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}, {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Partition", "Advance start date by", each if [#"Contract Length - Copy.1"] < 12 then 0 else 12 *([Index1]-1)),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom3",{"Index", "Start Date", "Custom", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by"}),
#"Added Custom4" = Table.AddColumn(#"Reordered Columns1", "Advance end date by", each if [#"Contract Length - Copy.1"]-[Advance start date by] > 12 then 12 else [#"Contract Length - Copy.1"]-[Advance start date by]),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom5",{"Index", "Start Date", "Custom", "Custom.1", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Start Date", "End Date", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Start Date"}, {"Custom.1", "End Date"}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Start Date", type date}, {"End Date", type date}}, "en-IN"),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type with Locale",{"Index"})
in
#"Removed Columns3"

Hope this helps.

Regular Visitor

## Re: Split Rows based on Condition

Hi Ashish,

thank you so much for your help. I am going to try it today and will you the updates.

Thanks

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 105 members 1,355 guests
Recent signins: