cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tejapowerbi123 Regular Visitor
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 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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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"),
    #"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

14 REPLIES 14
Highlighted
CR Member
Member

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

tejapowerbi123 Regular Visitor
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

CR Member
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

tejapowerbi123 Regular Visitor
Regular Visitor

Re: Split Rows based on Condition

Hi CR,

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

 

Super User
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)),
    #"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

tejapowerbi123 Regular Visitor
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

 

Let me know if you need more information.

Thanks

 

 

tejapowerbi123 Regular Visitor
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}}),


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
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"),
    #"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

tejapowerbi123 Regular Visitor
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

 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 294 members 3,090 guests
Please welcome our newest community members: