Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
3 years ago
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...
Need Help
  • danextian's avatar
    danextian
    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.

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
3 years ago

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"

bolfri's avatar
bolfri
Icon for Solution Sage rankSolution Sage
3 years ago

cgkas Try this one.

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 Start Date" = Table.TransformColumns(Source,{{"Start Date & Time", each DateTime.From(_, "en-us"), type date}}),
    #"Parsed End Date" = Table.TransformColumns(#"Parsed Start Date",{{"End Date & Time", each DateTime.From(_, "en-us"), type date}}),
    #"Inserted Start Date" = Table.AddColumn(#"Parsed End Date", "_Start Date", each DateTime.Date([#"Start Date & Time"]), type date),
    #"Inserted Start Time" = Table.AddColumn(#"Inserted Start Date", "_Start Time", each Time.From([#"Start Date & Time"]), type time),
    #"Inserted End Date" = Table.AddColumn(#"Inserted Start Time", "_End Date", each DateTime.Date([#"End Date & Time"]), type date),
    #"Inserted End Time" = Table.AddColumn(#"Inserted End Date", "_End Time", each Time.From([#"End Date & Time"]), type time),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted End Time",{"Start Date & Time", "End Date & Time"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "SplittingDates", each SplittingDates([_Start Date], [_Start Time], [_End Date], [_End Time])),
    #"Expanded SplittingDates" = Table.ExpandTableColumn(#"Invoked Custom Function", "SplittingDates", {"Start Date", "Start Time", "End Date", "End Time"}, {"Start Date", "Start Time", "End Date", "End Time"}),
    #"Removed Old Columns" = Table.RemoveColumns(#"Expanded SplittingDates",{"_Start Date", "_Start Time", "_End Date", "_End Time"})
in
    #"Removed Old Columns"