Forum Discussion

gabereal's avatar
gabereal
Frequent Visitor
2 years ago
Solved

Split Rows Based On Start/End Time. New Row per Day

I have data that needs to be split on a daily basis.   Item Name   Start Date Time   End Date Time A   11/19/2023 11:15:00 PM   11/20/2023 1:15:00 AM B   11/20/2023 02:15:00 PM ...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    gabereal 

     

        f = (row as record) as list =>
        if Date.From(row[End Date Time]) = Date.From(row[Start Date Time]) then row else
        [max_dt = row[End Date Time],
        gen = List.Generate(
            () => Record.TransformFields(row, {"End Date Time", (w) => Date.StartOfDay(Date.AddDays(row[Start Date Time], 1))}),
            (x) => x[Start Date Time] < max_dt,
            (x) => 
                [Item Name = x[Item Name], 
                Start Date Time = Date.StartOfDay(Date.AddDays(x[Start Date Time], 1)), 
                End Date Time = List.Min({max_dt, Date.AddDays(x[End Date Time], 1)})]
        )][gen],
    tbl = Table.TransformRows(your_table, f),
    z = Table.FromRecords(List.Combine(tbl))

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi gabereal, another approach.

 

Restult:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01De01DcyMDJWMDS0MjS1MjBQCPCFSBgZQCWg4o6+SrE60UpOqLIGRujajCASyHqcoVImuPSYoOmJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Name" = _t, #"Start Date Time" = _t, #"End Date Time" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date Time", type datetime}, {"End Date Time", type datetime}}, "en-US"),
    Ad_NewStartEnd = Table.AddColumn(ChangedType, "New StartEnd", each 
     [ start = Date.From([Start Date Time]),
       end = Date.From([End Date Time]),
       dates = List.Dates(Date.AddDays(start, 1), Duration.TotalDays(end - start) , #duration(1,0,0,0)),
       startCalc = if List.Count(dates) > 0 then {[Start Date Time]} & List.Transform(dates, each DateTime.From(_ & #time(0,0,0))) else {[Start Date Time]},
       endCalc = if List.Count(dates) > 0 then {DateTime.From(List.First(dates) & #time(0,0,0))} & List.Transform(List.RemoveLastN(dates, 1), each DateTime.From(Date.AddDays(_, 1) & #time(0,0,0))) & {[End Date Time]} else {[End Date Time]},
       toTable = Table.FromColumns({startCalc, endCalc}, type table[Start New = date, End New = date])
  ][toTable], type table),
    RemovedColumns = Table.RemoveColumns(Ad_NewStartEnd,{"Start Date Time", "End Date Time"}),
    ExpandedNewStartEnd = Table.ExpandTableColumn(RemovedColumns, "New StartEnd", {"Start New", "End New"}, {"Start Date Time", "End Date Time"}),
    ChangedType2 = Table.TransformColumnTypes(ExpandedNewStartEnd,{{"Start Date Time", type datetime}, {"End Date Time", type datetime}})
in
    ChangedType2