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

Get 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

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.