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

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.

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

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