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.
bolfri
Solution Sage
3 years agoI 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
cgkas
Helper V
3 years agoThanks for your help. It seems to work changing the format manually. Thanks for your help and time.