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

     

spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago

Here is a way to do it without going into the advanced editor and taking advantage of how power query will generate a list from 1 to 10 if you type {1 .. 10} :

 

Duplicate the start and end date columns.

Change columns types to decimal

Add custom column with the formula:

 

 

Table.FromColumns(
{
    {
        [#"Start Date Time - Copy"], 
        Number.RoundUp([#"Start Date Time - Copy"],0) 
        .. 
        Number.RoundDown([#"End Date Time - Copy"],0)
    },
    {
        Number.RoundUp([#"Start Date Time - Copy"],0) 
        .. 
        Number.RoundDown([#"End Date Time - Copy"],0),
        [#"End Date Time - Copy"]
    }
}, 
{"start","end"}
)

 

 

Expand.

Change to datetime.

Add custom column to detect for same start/end times and filter out.