Forum Discussion

guyinazo's avatar
guyinazo
Helper I
4 years ago
Solved

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 ...
  • lbendlin's avatar
    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".