Forum Discussion
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 |
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"
14 Replies
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"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
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"})
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
- 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
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
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
