- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Split Rows based on Condition
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 10:27 AM - edited 12-03-2018 10:37 AM
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 |
Solved! Go to Solution.
Accepted Solutions
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2018 04:10 PM
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"
All Replies
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 12:00 PM
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
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 12:28 PM
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
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 12:42 PM
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
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 03:09 PM
Hi CR,
thank you so much for your advice. I will wait for your response.
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 07:01 PM
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"
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-04-2018 06:56 AM
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
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-04-2018 09:08 AM
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"})
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-04-2018 07:46 PM
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.
Re: Split Rows based on Condition
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-05-2018 04:51 AM
Hi Ashish,
thank you so much for your help. I am going to try it today and will you the updates.
Thanks