Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Community,
I want to split rows based on days.Please see below Row Data and Final Data for sample.
If Days =<364 Days then not split just keep as it is but if Days>364 then split every row by 364 Days.
Example:
If Days are 1094 then split 3 rows for 364 Days & 364 Days & 364 Days.
if Days are 584 then split rows into 2,first rows is for 364 Days and second rows for 220.
ROW DATA | |||
ID | Begin | End | Days |
A | 8/15/2018 | 8/13/2021 | 1094 |
B | 3/13/2018 | 10/18/2019 | 584 |
C | 9/1/2018 | 8/31/2019 | 364 |
D | 11/1/2018 | 7/31/2019 | 272 |
FINAL DATA | |||
ID | Begin | End | Days |
A | 8/15/2018 | 8/14/2019 | 364 |
A | 8/15/2019 | 8/13/2020 | 364 |
A | 8/14/2020 | 8/13/2021 | 364 |
B | 3/13/2018 | 3/12/2019 | 364 |
B | 3/13/2019 | 10/18/2019 | 220 |
C | 9/1/2018 | 8/31/2019 | 364 |
D | 11/1/2018 | 7/31/2019 | 272 |
Solved! Go to Solution.
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.
@Anonymous seems like there is mistake in calculation, if you look at A, 1094 days divided by 3 times x 364 make it is 1092 and I believe there should be fourth row with 2 days, isn't it?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi Perry,
You can think in that way,
Days=1094 is 36 Months
Days=584 is 19 Months
Days=364 is 12 Months
Days=272 is 9 Months
@Anonymous just checking the days values are always going to be on of these options:
1094
584
364
272
Reason is to understand what should be the best logic to achieve it, if these are the only four options it will be a different logic
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
As per my understadning,
Example:if Days are <364 (12 Months or Less) then keep only one rows,
if Days>364 then split rows according to days,it means if Days are 584 (19 Months:1 row for 12 months and 2 row for 7 month) then split first row for 364 Days and Second row should be (584-364).if days are 1094 then first row should be 364,second rows should be 364 and thrid row should be 1094-(364+364)=366.
Let me know if you need more clarifications.
Hi,
Your logic is inconsistent. For 1094 rows, there should be 4 rows (the last row showing 2 days). How many rows would you expect if there were 1200 days?
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,
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.
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 Perry,
Thank you so much for your comments.
As per the requirements it should go by 364 Days +364 Days+366 Days and require only 3 rows (36 Months).
Thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
70 | |
67 |