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.
Hi bolfri , thanks for your answer. I've tried but I'm getting error in column "Start Date" and "End Date" in step #"Change Type"
I think it's because I am using date like dd/mm/rrrr and you propobly using mm/dd/rrrr. Is that correct? If then then to check if this code works for you simly change the source date from json from 10/13/2022 to 13/10/2022. Code will do the rest. If the code works for you and you will receive what you want - i will change the type detection for you to different format type.
Maybe the pbix file will help: https://we.tl/t-cgKwLuSr2j
- cgkas3 years ago
Helper V
Thanks for your help. It seems to work changing the format manually. Thanks for your help and time.