Splitting dates and time in 2 rows

Duplicate Column, then Transform New Column and Select Hour. Repeat as needed for other time segments. Keeping data in a single row may be simpler for calculating time difference.

Hello,

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 👍

Follow me on Linkedin

cgkas split column by delimiter

Not sure if your final ask easily do-able, to my knowledge. However, I do have a suggested that was not listed above, in regards to splitting up your columns:

• In PQ, with the initial date column selected: go to "Add Column"
• Select the Date drop down, and choose "Date Only"
• Follow the same steps, but use the Time option (and "Time Only) instead of Date.

The above should assist in breaking up the column up into date and time. Question--What's the purpose of breaking out these dates/times in such a way? This context might help others by knowing what your end goal is.

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"

Hi cgkas ,

Based on your screenshots, creating custom column with a list of dates from existing date columns should do.

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) )

The above will create a column of lists which you can then expand into rows.

Try this in a blank query

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}})
in
    #"Changed Type"

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"