cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Re: Split Rows based on Condition

Hi Ashish,

Thank you so much it is working fine for all the months,Only last confusion for 12 Months 2 Rows start date is showing 1/1/2016.

How to change it to 1/1/2015. 

powerbiscreen.png

Highlighted
Super User IV
Super User IV

Re: Split Rows based on Condition

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"

Untitled.png


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

View solution in original post

Highlighted
Helper II
Helper II

Re: Split Rows based on Condition

Hi Ashish,

thank you so much for your suggestion.still end date is not shwoing right in 2 rows.It works for me.

finalpicte.png

Highlighted
Frequent Visitor

Re: Split Rows based on Condition

Hi Ashish,

 

I am working on getting the similar concept but not getting around anywhere.

In my Room booking scenario I have to split my rows hourly .

 

RoomIDStart DateEnd Date Duration 
109:0011:00120 
209:0010:0060 
311:0012:3090 

RoomID 1: should repeat 2 times (09:00-10:00 ,10:00-11:00)

Room ID 2: no repeat

Roomid 3: repeat 2 times 

How can I achieve this in power bi?

Highlighted
Super User IV
Super User IV

Re: Split Rows based on Condition

Hi,

I only know how to make it work for Dates (not for time stamps).  Sorry, i cannot help.


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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors