Forum Discussion
Solved
How to split datetime range into multiple rows based on 24 hours intervals
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: ...
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"
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
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"Excellent, thank you!