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"
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.
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!
