Forum Discussion

CornelisV's avatar
CornelisV
Helper IV
1 year ago
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: ...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago
    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"
lbendlin's avatar
lbendlin
Super User
1 year ago
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's avatar
CornelisV
Helper IV
1 year ago

Excellent, thank you!