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
guyinazo
Helper I
Helper I

Splitting a row into two rows

I asked about this yesterday, but still am not clear.  I am sure I will need to do a union, join or something else.  I have a dataset that has starting times and ending times.  If the two times span over two dates (for example start time is 9PM and end time is 3AM), then it needs to be split into two rows.  Using the example it would be a start time of 9PM to end time of 00 and then a second row with a start time of 00 and end time of 3AM (or 0300).  Each row would duplicate ALL data fields except for the date and time fields.

 

Thanks for any help and information

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Interesting.  Is it ever possible that an event would span more than two days? Assuming that that is possible below is a formula that splits this

lbendlin_0-1634695021193.png

into this

lbendlin_1-1634695057965.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLCoUwDEW3Ih0LJmlt65u5AMF56f634ScR37UKpYN7uB9SimNy/f4NngYh4Y75R7S/bl0MsIJs+ry42hc3KeWE9HJxVn1EV1YqRqebgh7vtMOVLDM2maALuqLtbyJPOajscd8InuYW4fUUwWZ8rHsd5w1eTfS8n1UlrBLYER5VJjNWscLcQJD/BtS6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Start = _t, End = _t]),
    DateRange = (start,end) as list =>  List.Generate(()=>start, each _ <= end, each _ + #duration(1,0,0,0)),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Start", type datetime}, {"End", type datetime}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Range", each DateRange(Date.From([Start]), Date.From([End]))),
    #"Expanded Range" = Table.ExpandListColumn(#"Invoked Custom Function", "Range"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Range",each [Start],each if [Start]<DateTime.From([Range]) then DateTime.From([Range]) else [Start] ,Replacer.ReplaceValue,{"Start"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value",each [End],each if Date.From([End])>Date.From([Range]) then DateTime.From([Range])+#duration(1,0,0,0) else [End] ,Replacer.ReplaceValue,{"End"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value2",{{"End", type datetime}, {"Start", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Column1", "Start", "End"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

Interesting.  Is it ever possible that an event would span more than two days? Assuming that that is possible below is a formula that splits this

lbendlin_0-1634695021193.png

into this

lbendlin_1-1634695057965.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLCoUwDEW3Ih0LJmlt65u5AMF56f634ScR37UKpYN7uB9SimNy/f4NngYh4Y75R7S/bl0MsIJs+ry42hc3KeWE9HJxVn1EV1YqRqebgh7vtMOVLDM2maALuqLtbyJPOajscd8InuYW4fUUwWZ8rHsd5w1eTfS8n1UlrBLYER5VJjNWscLcQJD/BtS6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Start = _t, End = _t]),
    DateRange = (start,end) as list =>  List.Generate(()=>start, each _ <= end, each _ + #duration(1,0,0,0)),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Start", type datetime}, {"End", type datetime}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Range", each DateRange(Date.From([Start]), Date.From([End]))),
    #"Expanded Range" = Table.ExpandListColumn(#"Invoked Custom Function", "Range"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Range",each [Start],each if [Start]<DateTime.From([Range]) then DateTime.From([Range]) else [Start] ,Replacer.ReplaceValue,{"Start"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value",each [End],each if Date.From([End])>Date.From([Range]) then DateTime.From([Range])+#duration(1,0,0,0) else [End] ,Replacer.ReplaceValue,{"End"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value2",{{"End", type datetime}, {"Start", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Column1", "Start", "End"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

 

Thanks for this.  It is for a time sheet app for a company that runs 24x7.  So yes, there are many that start around 2200 to 2300 and then leave after midnight.  For some accounting purposes, they need to accurately report on total hours BY the day.  And thanks for this.  I did get other help, combined that help, including yours and it works.

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.