Extract 24 hour date/time ranges as additional rows

Hi, Lucas01 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may paste the following m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbLUNzTRNzIwMlAwNLEyMAAiVEEzqGCsTrSSER71pljUG+NRb4GuPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, #"Start Date/Time" = _t, #"End Date/Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", Int64.Type}, {"Start Date/Time", type datetime}, {"End Date/Time", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Re", each let startdate=Date.From([#"Start Date/Time"]),
enddate=Date.From([#"End Date/Time"])
in
List.Generate(
    ()=>[s=startdate,e=startdate],
    each [s]<=enddate,
    each [s=[s]+#duration(1,0,0,0),e=[e]+#duration(1,0,0,0)]
)),
    #"Expanded Re" = Table.ExpandListColumn(#"Added Custom1", "Re"),
    #"Expanded Re1" = Table.ExpandRecordColumn(#"Expanded Re", "Re", {"s", "e"}, {"Re.s", "Re.e"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Re1", "NewStart", each let date=[Re.s],time=Time.From([#"Start Date/Time"]),
task=[Task],
tab = Table.SelectRows(#"Expanded Re1",each [Task]=task),
mindate=Table.Min(tab,"Re.s")[Re.s]
in 
if [Re.e]=mindate
then
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    Time.Hour(time),
    Time.Minute(time),
    Time.Second(time)
)
else 
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    0,
    0,
    0
)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "NewEnd", each let date=[Re.e],time=Time.From([#"End Date/Time"]),
task=[Task],
tab = Table.SelectRows(#"Expanded Re1",each [Task]=task),
mindate=Table.Max(tab,"Re.e")[Re.e]
in 
if [Re.e]=mindate
then
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    Time.Hour(time),
    Time.Minute(time),
    Time.Second(time)
)
else 
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    23,
    59,
    59
)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date/Time", "End Date/Time", "Re.s", "Re.e"})
in
    #"Removed Columns"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.