Forum Discussion

cgkas's avatar
cgkas
Helper 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.

bolfri's avatar
bolfri
Solution Sage
3 years ago

I think this will be the best solution for your case. It's a combination of my first answer after some improvements based on a solution that danextian propose in his answer.

 

Step 1. Create a funtion. Let's call it: SplittingDatesFunctions

let getParameters = (StartDate as datetime, EndDate as datetime) =>
    
    let
        Start = Date.From(StartDate),
        End = Date.From(EndDate),
        CountDays = Duration.Days(End-Start) +1,
        DateList = List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),
        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Start Date"}, null, ExtraValues.Error),
        #"Add Custom Start Time" = Table.AddColumn(#"Converted to Table", "Start Time", each if [Start Date] = Start then Time.From(StartDate) else #time(0,0,0)),
        #"Add End Date" = Table.AddColumn(#"Add Custom Start Time", "End Date", each [Start Date]),
        #"Add Custom End Time" = Table.AddColumn(#"Add End Date", "End Time", each if [Start Date] = End then Time.From(EndDate) else #time(23,59,59)),
        FinishedDateList = Table.TransformColumnTypes(#"Add Custom End Time",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}})

    in
        #"FinishedDateList"
        
in
    getParameters

Step 2. Prepare some table with Start and End DateTime fields.

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}})
in
    #"Parsed End Date"

It should looks like this:

Step 3. Add Column with "Invoke Custom Function" 

Name the new colum as you want, select function that you have added and set up parameters to the fields that we have in table

Step 4. Expand new columns 

 

Step 5. Delete old columns

 

Step 6. Leave a Kudos for me and danextian ðŸ™‚

Full steps based on SplittingDatesFunctions function.

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}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Parsed End Date", "SplittingDatesFunctions", each SplittingDatesFunctions([#"Start Date & Time"], [#"End Date & Time"])),
    #"Expanded SplittingDatesFunctions" = Table.ExpandTableColumn(#"Invoked Custom Function", "SplittingDatesFunctions", {"Start Date", "Start Time", "End Date", "End Time"}, {"Start Date", "Start Time", "End Date", "End Time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded SplittingDatesFunctions",{"Start Date & Time", "End Date & Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}})
in
    #"Changed Type"