Forum Discussion
How to split datetime range into multiple rows based on 24 hours intervals
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"
Power Query Approach:
In Power Query, start by creating a new query or modifying your existing query.
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)
)
Expand the list to turn each entry into a new row.
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.
Remove unnecessary columns, leaving the newly created datetime ranges.
This approach in Power Query will generate the desired split for 24-hour intervals.
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
