How to split datetime range into multiple rows based on 24 hours intervals

[CornelisV]

Dear all,

I think this topic has been discussed before, but I would like to know, as a beginner, how to place the time range in rows of a table that is newly created.

This is the input dataset:

ID	Starttms	Eindtms
1	13-09-2024 23:45	14-09-2024 22:15
2	14-09-2024 22:15	16-09-2024 02:15
3	16-09-2024 02:15	17-09-2024 06:00

The desired output is here:

ID	Starttms	Eindtms
1	13-09-2024 23:45	13-09-2024 23:59
1	14-09-2024 0:00	14-09-2024 22:15
2	14-09-2024 22:15	14-09-2024 23:59
2	15-09-2024 00:00	15-09-2024 23:59
2	16-09-2024 00:00	16-09-2024 02:15
3	16-09-2024 02:15	17-09-2024 06:00

As you can see, the datetime range must be split into 24 hours time interval. 

Could you please give advice how to craete a new table with split time range on 24 hour base?

Best regards,

Cornelis

[lbendlin]

Not sure why you would need that?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcq5DQAhDAXRVlY/BskXoHUrlvtvA0ggIX0zEWAUsFb6q5DYJ+rWNtklcW7IEpBXWNQP0Xn1FRaNS92JkDkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Starttms = _t, Eindtms = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Starttms", type datetime}, {"Eindtms", type datetime}},"nl"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Timestamp", each List.DateTimes([Starttms],Number.From(([Eindtms]-[Starttms])*1440)+1,#duration(0,0,1,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Timestamp"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Date", each Date.From([Timestamp])),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"ID", "Date"}, {{"Starttms", each List.Min([Timestamp]), type datetime}, {"Eindtms", each List.Max([Timestamp]), type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Date"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[CornelisV]

Thank you for your very swift reply. That looks promising.

Could you please indicate me which part of the source code should be replaced?

This is the original source from input table:

let
Source = Excel.Workbook(File.Contents("C:\Users\c754781\OneDrive - CVe\Power BI training\Date time split2.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Starttms", type datetime}, {"Eindtms", type datetime}})
in
#"Changed Type"

Best regards,

Cornelis

[lbendlin · Accepted solution]

let
Source = Excel.Workbook(File.Contents("C:\Users\c754781\OneDrive - CVe\Power BI training\Date time split2.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Starttms", type datetime}, {"Eindtms", type datetime}},"nl"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Timestamp", each List.DateTimes([Starttms],Number.From(([Eindtms]-[Starttms])*1440)+1,#duration(0,0,1,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Timestamp"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Date", each Date.From([Timestamp])),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"ID", "Date"}, {{"Starttms", each List.Min([Timestamp]), type datetime}, {"Eindtms", each List.Max([Timestamp]), type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Date"})
in
    #"Removed Columns"

[CornelisV]

Excellent, thank you!

[123abc]

Power Query Approach:

1. In Power Query, start by creating a new query or modifying your existing query.

2. Add a Custom Column to calculate the number of days between Starttms and Eindtms.

Duration.Days([Eindtms] - [Starttms])

Add a conditional column or Use a custom formula to create multiple rows for each day within the range:

• First, generate a list of dates starting from the Starttms until the Eindtms by creating a new column:

List.Generate(
() => [Starttms],
each _ <= [Eindtms],
each DateTime.AddDays(_, 1)
)

1. Expand the list to turn each entry into a new row.

2. Create a new column to hold the Start Time and End Time for each row:

   • For the first day, the start time will be the actual start time, and the end time will be 23:59.
   • For intermediate days, the start time will be 00:00, and the end time will be 23:59.
   • For the last day, the start time will be 00:00, and the end time will be the actual Eindtms.

3. Remove unnecessary columns, leaving the newly created datetime ranges.

This approach in Power Query will generate the desired split for 24-hour intervals.

[CornelisV]

Hello,

Could you please make a screenshot for this step?

Add a conditional column or Use a custom formula to create multiple rows for each day within the range:

• First, generate a list of dates starting from the Starttms until the Eindtms by creating a new column:

List.Generate(
() => [Starttms],
each _ <= [Eindtms],
each DateTime.AddDays(_, 1)
)

It is for me not very clear wihich selection you did make.

Thank you,

Cornelis