Forum Discussion
cgkas
Helper V
3 years agoSplitting 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...
- 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.
Idrissshatila
Super User
3 years agoHello,
you go to power query and click on the date & time column then in the tab you go to add column => extract => Text after delimiter=> and in the delimeter you put spaceand enter and it will split it in another column then you change the datatype of the date& time as date.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍