Forum Discussion
Solved
Splitting dates and time in 2 rows
Hi all, I have the "start date and time" that begins in one date and sometimes ends in next day. For this cases I'd like to split in two rows from "start date" time XX:XX until same date up to 2...
I've realized you needed more columns for the time. Here's the revised M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcrBCcAwDAPAVYrfgViyTUCrBO+/RiEtpd/j9jb4REw6eTGUKZSNo/mq6AKsx8nML2Mp+OSf1lKWdd8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date & Time" = _t, #"End Date & Time" = _t]), #"Parsed Date" = Table.TransformColumns(Source,{{"Start Date & Time", each DateTime.From(_, "en-us"), type date}}), #"Parsed Date1" = Table.TransformColumns(#"Parsed Date",{{"End Date & Time", each DateTime.From(_, "en-us"), type date}}), #"Added Custom" = Table.AddColumn(#"Parsed Date1", "CompleteDates", each let start = Date.From([#"Start Date & Time"]), end = Date.From([#"End Date & Time"]), count = Duration.Days(end-start) +1 in List.Dates ( start, count, #duration(1,0,0,0) ), type list), #"Expanded CompleteDates" = Table.ExpandListColumn(#"Added Custom", "CompleteDates"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded CompleteDates",{{"CompleteDates", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"CompleteDates", "StartDate"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "StartTime", each let start = [#"Start Date & Time"], starttime = Time.From([#"Start Date & Time"]) in if Date.From([#"Start Date & Time"]) = [StartDate] then starttime else #time(0,0,0), Time.Type), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "EndTime", each let end = [#"End Date & Time"], endtime = Time.From(end) in if Date.From(end) <> [StartDate] then #time(23,59,59) else endtime, Time.Type), #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom2", "StartDate", "EndDate"), #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Start Date & Time", "End Date & Time", "StartDate", "StartTime", "EndDate", "EndTime"}) in #"Reordered Columns"You need to format the time columns accordingly in the report builder.
Hi cgkas ,
Based on your screenshots, creating custom column with a list of dates from existing date columns should do.
let
start = Date.From([#"Start Date & Time"]),
end = Date.From([#"End Date & Time"]),
count = Duration.Days(end-start) +1
in List.Dates ( start, count, #duration(1,0,0,0) )The above will create a column of lists which you can then expand into rows.
Try this in a blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcrBCcAwDAPAVYrfgViyTUCrBO+/RiEtpd/j9jb4REw6eTGUKZSNo/mq6AKsx8nML2Mp+OSf1lKWdd8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date & Time" = _t, #"End Date & Time" = _t]),
#"Parsed Date" = Table.TransformColumns(Source,{{"Start Date & Time", each DateTime.From(_, "en-us"), type date}}),
#"Parsed Date1" = Table.TransformColumns(#"Parsed Date",{{"End Date & Time", each DateTime.From(_, "en-us"), type date}}),
#"Added Custom" = Table.AddColumn(#"Parsed Date1", "CompleteDates", each let
start = Date.From([#"Start Date & Time"]),
end = Date.From([#"End Date & Time"]),
count = Duration.Days(end-start) +1
in List.Dates ( start, count, #duration(1,0,0,0) ), type list),
#"Expanded CompleteDates" = Table.ExpandListColumn(#"Added Custom", "CompleteDates"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded CompleteDates",{{"CompleteDates", type date}})
in
#"Changed Type"I've realized you needed more columns for the time. Here's the revised M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcrBCcAwDAPAVYrfgViyTUCrBO+/RiEtpd/j9jb4REw6eTGUKZSNo/mq6AKsx8nML2Mp+OSf1lKWdd8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date & Time" = _t, #"End Date & Time" = _t]),
#"Parsed Date" = Table.TransformColumns(Source,{{"Start Date & Time", each DateTime.From(_, "en-us"), type date}}),
#"Parsed Date1" = Table.TransformColumns(#"Parsed Date",{{"End Date & Time", each DateTime.From(_, "en-us"), type date}}),
#"Added Custom" = Table.AddColumn(#"Parsed Date1", "CompleteDates", each let
start = Date.From([#"Start Date & Time"]),
end = Date.From([#"End Date & Time"]),
count = Duration.Days(end-start) +1
in List.Dates ( start, count, #duration(1,0,0,0) ), type list),
#"Expanded CompleteDates" = Table.ExpandListColumn(#"Added Custom", "CompleteDates"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded CompleteDates",{{"CompleteDates", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"CompleteDates", "StartDate"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "StartTime", each let start = [#"Start Date & Time"],
starttime = Time.From([#"Start Date & Time"])
in if Date.From([#"Start Date & Time"]) = [StartDate] then starttime else #time(0,0,0), Time.Type),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "EndTime", each let end = [#"End Date & Time"],
endtime = Time.From(end)
in if Date.From(end) <> [StartDate] then #time(23,59,59) else endtime, Time.Type),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom2", "StartDate", "EndDate"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Start Date & Time", "End Date & Time", "StartDate", "StartTime", "EndDate", "EndTime"})
in
#"Reordered Columns"
You need to format the time columns accordingly in the report builder.
It seems it works just fine now. Thanks so much for your help.
