Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Split Rows based on Days

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
IDBeginEndDays
A8/15/20188/13/20211094
B3/13/201810/18/2019584
C9/1/20188/31/2019364
D11/1/20187/31/2019272
    
    
FINAL DATA
IDBeginEndDays
A8/15/20188/14/2019364
A8/15/20198/13/2020364
A8/14/20208/13/2021364
B3/13/20183/12/2019364
B3/13/201910/18/2019220
C9/1/20188/31/2019364
D11/1/20187/31/2019272
1 ACCEPTED 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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

@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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

communitry.jpg

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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   AmountStart Date      End Date            YearDays
1   261,600.0001/06/201631/12/201920161308

 

Result:

Index   AmountStart Date      End Date            YearDays
1     72,800.0001/06/201631/05/20172016364
1     72,800.0001/06/201731/05/20182017364
1     72,800.0001/06/201831/05/20192018364
1     42,600.0001/06/201931/12/20192019213

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

My previous post is now edited.

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.