Forum Discussion
Split Rows based on Days
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Begin", type date}, {"End", type date}, {"Days", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Remainder", each Number.Mod([Days],364)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Integer", each Number.IntegerDivide([Days],364)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rows to be created", each if [Integer]=0 then 1 else if [Remainder]=0 then [Integer] else [Integer]+1), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each {Number.From(1)..Number.From([Rows to be created])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"), #"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Number of days", each if [Days]<=364 then [Days] else if [Custom]*364<=[Days] then 364 else [Remainder]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Days", "Remainder", "Integer", "Rows to be created", "Custom"}) in #"Removed Columns"Hope this helps.
Hi Ashish,
Below screenshot you are looking , I have copied from excel to power bi and result is correct.Suppose ID=A has 36 Months contract or 1094 Days. Begin column and End column resule should match and i think that's why we have 364 Days.
Can you suggest me how to split 1094 days in 4 rows split?
Thanks
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Begin", type date}, {"End", type date}, {"Days", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Remainder", each Number.Mod([Days],364)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Integer", each Number.IntegerDivide([Days],364)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rows to be created", each if [Integer]=0 then 1 else if [Remainder]=0 then [Integer] else [Integer]+1),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each {Number.From(1)..Number.From([Rows to be created])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"),
#"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Number of days", each if [Days]<=364 then [Days] else if [Custom]*364<=[Days] then 364 else [Remainder]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Days", "Remainder", "Integer", "Rows to be created", "Custom"})
in
#"Removed Columns"Hope this helps.
I tried to split total contract value (TCV) into annual contract value (ACV) but with no luck.
My data has a mixture of contracts duration between 1 day to 5years+
All contracts =< 1 year require no attention but if a contract is eg 4 year long, it should be split into 4 rows as follows
Source:
Index Amount Start Date End Date Year Days 1 261,600.00 01/06/2016 31/12/2019 2016 1308 Result:
Index Amount Start Date End Date Year Days 1 72,800.00 01/06/2016 31/05/2017 2016 364 1 72,800.00 01/06/2017 31/05/2018 2017 364 1 72,800.00 01/06/2018 31/05/2019 2018 364 1 42,600.00 01/06/2019 31/12/2019 2019 213 This is what I am trying to achieve. In this example Im getting 1308 days (31/12/2019 - 01/06/2016) but when i split into 4 years somehow i am getting 1305 but that is another matter.
Hi,
Your question is not clear and neither is the data that you have shared. Share data in a format that can be pasted in an MS Excel file, desribe the business question and show the expected result.
My previous post is now edited.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Begin", type date}, {"End", type date}, {"Days", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Remainder", each Number.Mod([Days],364)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Integer", each Number.IntegerDivide([Days],364)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rows to be created", each if [Integer]=0 then 1 else if [Remainder]=0 then [Integer] else [Integer]+1), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each {Number.From(1)..Number.From([Rows to be created])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"), #"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Number of days", each if [Days]<=364 then [Days] else if [Custom]*364<=[Days] then 364 else [Remainder]), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.1", each if [Number of days]=364 then Date.AddDays(Date.AddYears([Begin],[Custom]),-1) else [End]), #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Custom.2", each Date.AddYears([Begin],[Custom]-1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom6",{"Days", "Remainder", "Integer", "Rows to be created", "Custom", "End","Begin"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Custom.2", "Custom.1", "Number of days"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Begin"}, {"Custom.1", "End"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Begin", type date}, {"End", type date}}) in #"Changed Type1"Hope this helps.
