Reply
Regular Visitor
Posts: 21
Registered: ‎12-03-2018
Accepted Solution

Split Rows based on Condition

[ Edited ]

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 DateEnd DateContract Length 
1/1/201531/12/201512 Months 
1/1/201531/07/20157 Months 
1/1/201531/07/201619 Months 
1/1/201531/12/201624 Months 
1/1/201531/07/201843 Months 
    
    
Start DateEnd DateContract LengthRow Split Comments
1/1/201531/12/201512 Monthsfor 12 Months 2 row split 
1/1/201531/12/201512 Monthsfor 12 Months 2 row split 
    
1/1/201531/07/20157 Monthsfor 7 Months 1 Row Split
    
1/1/201531/12/201519 Monthsfor 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split
1/1/201631/07/201619 Monthsfor 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split
    
1/1/201531/12/201524 Monthsfor 24 Months 2 row split of 12 Months 
1/1/201631/12/201624 Monthsfor 24 Months 2 row split of 12 Months 
    
1/1/201531/12/201543 Monthsfor 43 Months 3 row split of 12 Months and 1 Row with 7 Months
1/1/201631/12/201643 Monthsfor 43 Months 3 row split of 12 Months and 1 Row with 7 Months
1/1/201731/12/201743 Monthsfor 43 Months 3 row split of 12 Months and 1 Row with 7 Months
1/1/201831/07/201843 Monthsfor 43 Months 3 row split of 12 Months and 1 Row with 7 Months

Accepted Solutions
Super User
Posts: 3,918
Registered: ‎01-14-2017

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"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 1, 1),
    #"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)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {Number.From(1)..Number.From([Custom])}),
    #"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)),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each if [#"Contract Length - Copy.1"]=12 then Date.AddMonths([Start Date],0) else Date.AddMonths([Start Date],[Advance start date by])),
    #"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]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.1", each Date.AddDays(Date.AddMonths([Custom],[Advance end date by]),-1)),
    #"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"

Untitled.png

View solution in original post


All Replies
CR Member
Member
Posts: 53
Registered: ‎04-20-2017

Re: Split Rows based on Condition

Hi @tejapowerbi123

 

I advise you to follow these steps:

  • open your query editor
  • 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

1.png

 

  • 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

 

2.png

 

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

 

Regards,

CR

Regular Visitor
Posts: 21
Registered: ‎12-03-2018

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

CR Member
Member
Posts: 53
Registered: ‎04-20-2017

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

Regular Visitor
Posts: 21
Registered: ‎12-03-2018

Re: Split Rows based on Condition

Hi CR,

thank you so much for your advice. I will wait for your response.

 

Super User
Posts: 3,918
Registered: ‎01-14-2017

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)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {Number.From(1)..Number.From([Custom])}),
    #"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"

 

Untitled.png

Regular Visitor
Posts: 21
Registered: ‎12-03-2018

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

 

Let me know if you need more information.

Thanks

 

 

Highlighted
Regular Visitor
Posts: 21
Registered: ‎12-03-2018

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}}),


AddedYearDates = 
Table.AddColumn(
Source, 
"Year Start", 
(fnAddYear) => List.Generate(
() => [YearDate = fnAddYear[start], Counter = 1],
each [YearDate] < fnAddYear[end],
each [YearDate = Date.AddYears(fnAddYear[start],[Counter]),
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"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Date.AddYears([Year Start],1)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each Date.AddDays([Custom],-1)),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1] <= [end] then [Custom.1] else [end]),
#"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
Posts: 3,918
Registered: ‎01-14-2017

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"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 1, 1),
    #"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)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {Number.From(1)..Number.From([Custom])}),
    #"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)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each Date.AddMonths([Start Date],[Advance start date by])),
    #"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]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.1", each Date.AddDays(Date.AddMonths([Custom],[Advance end date by]),-1)),
    #"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.

 

Untitled.png

Regular Visitor
Posts: 21
Registered: ‎12-03-2018

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