Forum Discussion

CornelisV's avatar
CornelisV
Icon for Helper IV rankHelper 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"
CornelisV's avatar
CornelisV
Icon for Helper IV rankHelper IV
1 year ago

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's avatar
lbendlin
Icon for Super User rankSuper 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
    Icon for Helper IV rankHelper IV
    1 year ago

    Excellent, thank you!