Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
into this
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".
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
into this
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |