Forum Discussion
Splitting a row into two rows
- 4 years ago
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.