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))

     

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

Here's another method using List.Accumulate

 

Data

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Item Name", type text}, {"Start Date Time", type datetime}, {"End Date Time", type datetime}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
    let 

    //create list of included days
      dys = List.DateTimes(
              Date.StartOfDay([Start Date Time]),
              Duration.Days(Date.EndOfDay([End Date Time]) - Date.StartOfDay([Start Date Time]))+1,
              #duration(1,0,0,0)),

    //create List of datetimes associated with each day
    // then add the last [End Date Time] to the list
      dts = List.Accumulate(
        dys,
        {},
        (s,c)=> s & {List.Max({[Start Date Time],c})}) & {[End Date Time]},

    //Turn the list into a Table by shifting the list up one to 
    // reflect start and end date/times
      tbl = Table.FromColumns(
        {List.RemoveLastN(dts,1)} &
        {List.RemoveFirstN(dts,1)},
        {"Start Date Time", "End Date Time"})   
  in tbl, type table[Start Date Time=datetime, End Date Time=datetime]),
  
  //Remove original datetime columns
  // then expand the generated table list
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start Date Time", "End Date Time"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Start Date Time", "End Date Time"})
in
    #"Expanded Custom"

 

Results