Forum Discussion
Splitting dates and time in 2 rows
- 3 years ago
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.
Not sure if your final ask easily do-able, to my knowledge. However, I do have a suggested that was not listed above, in regards to splitting up your columns:
- In PQ, with the initial date column selected: go to "Add Column"
- Select the Date drop down, and choose "Date Only"
- Follow the same steps, but use the Time option (and "Time Only) instead of Date.
The above should assist in breaking up the column up into date and time. Question--What's the purpose of breaking out these dates/times in such a way? This context might help others by knowing what your end goal is.
igrandey89 Thanks for your suggestion. Following your steps is not selectable the option "date only". I'm able to split the columns, the issue is how to dynamically add a second row when "start date" and "end date" are differents. The purporse to do this is because the origin data has different start/end dates and is needed to enter this data in a form that only accepts that start date = end date.