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.

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

I hope that this function will help you.

 

Step 1. Add a new function. Let's call it SplittingDates

let getParameters = (StartDate as date, StartTime as time, EndDate as date, EndTime as time) =>
    
    let
        _DifferentDays = StartDate <> EndDate,
        _CustomEndDate = if _DifferentDays then StartDate else EndDate,
        _CustomEndTime = if _DifferentDays then #time(23,59,59) else EndTime,
        _CustomStartDate = if _DifferentDays then EndDate else StartDate,
        _CustomStartTime = if _DifferentDays then #time(0,0,0) else StartTime,
        #"NewRows" = #table(
            type table
                [
                    #"Start Date"=date, 
                    #"Start Time"=time,
                    #"End Date"=date,
                    #"End Time"=time
                ],  
            {
            {StartDate,StartTime,_CustomEndDate,_CustomEndTime},
            {_CustomStartDate,_CustomStartTime,EndDate,EndTime}
            }
    ),
    #"Distinct" = Table.Distinct(NewRows)
    in
        #"Distinct"
in
    getParameters

2. Prepare basic table

What I understood is that you can create a table like this:

If no, you can use my 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]),
    #"Add Start Date" = Table.AddColumn(Source, "Start Date", each Text.Combine({Text.Middle([#"Start Date & Time"], 3, 3), Text.Start([#"Start Date & Time"], 2), Text.Middle([#"Start Date & Time"], 5, 5)}), type text),
    #"Add Start Time" = Table.AddColumn(#"Add Start Date", "Start Time", each Text.AfterDelimiter([#"Start Date & Time"], " "), type text),
    #"Add End Date" = Table.AddColumn(#"Add Start Time", "End Date", each Text.Combine({Text.Middle([#"End Date & Time"], 3, 3), Text.Start([#"End Date & Time"], 2), Text.Middle([#"End Date & Time"], 5, 5)}), type text),
    #"Add End Time" = Table.AddColumn(#"Add End Date", "End Time", each Text.AfterDelimiter([#"End Date & Time"], " "), type text),
    #"Removed Oryginal Columns" = Table.RemoveColumns(#"Add End Time",{"Start Date & Time", "End Date & Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Oryginal Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}})
in
    #"Changed Type"

3. Invoke function

Add Column > Invoke Custom Funtion and select column that matches Start and End Date/Time.

4. Expand new data

 

5. Delete old columns

6. Rename new one (if needed)

Whole 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]),
    #"Add Start Date" = Table.AddColumn(Source, "Start Date", each Text.Combine({Text.Middle([#"Start Date & Time"], 3, 3), Text.Start([#"Start Date & Time"], 2), Text.Middle([#"Start Date & Time"], 5, 5)}), type text),
    #"Add Start Time" = Table.AddColumn(#"Add Start Date", "Start Time", each Text.AfterDelimiter([#"Start Date & Time"], " "), type text),
    #"Add End Date" = Table.AddColumn(#"Add Start Time", "End Date", each Text.Combine({Text.Middle([#"End Date & Time"], 3, 3), Text.Start([#"End Date & Time"], 2), Text.Middle([#"End Date & Time"], 5, 5)}), type text),
    #"Add End Time" = Table.AddColumn(#"Add End Date", "End Time", each Text.AfterDelimiter([#"End Date & Time"], " "), type text),
    #"Removed Oryginal Columns" = Table.RemoveColumns(#"Add End Time",{"Start Date & Time", "End Date & Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Oryginal Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "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"}, {"splittingDates.Start Date", "splittingDates.Start Time", "splittingDates.End Date", "splittingDates.End Time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded splittingDates",{"Start Date", "Start Time", "End Date", "End Time"})
in
    #"Removed Columns"
  • 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

      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

       

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

        Thanks for your help. It seems to work changing the format manually. Thanks for your help and time.

    • 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"