Split data into right date

[Mahmed1]

Hi - not sure how complexed this is

i have a table where i have 

Start Date Time  End Date Time

19/12/23 22:35.  20/12/23 01:00

20/12/23. 08:00.  20/12/23 16:00 etc

all i want to do is if the start date time and end time overlap i need to separate them into its own rows else keep the row so it should look like this

first row overlaps over 2 days so i split till midnight and then follow on from

midnight

19/12/23 22:35.  19/12/23 00:00

20/12/23 00:00   20/12/23 01:00

20/12/23. 08:00.  20/12/23 16:00

hope this makes sense

[tackytechtom · Accepted solution]

Hi @Mahmed1 ,

Apologise! 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUNzTSNzJWMDKyMjZV0lEyMoAKGBhaGRgoxepEw4X0FAwsQGJIigzNwIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date Time" = _t, #"End Date Time" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Start Date Time.1", "Start Date Time.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "End Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"End Date Time.1", "End Date Time.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Start Date Time.1", "Start Date"}, {"Start Date Time.2", "Start Time"}, {"End Date Time.1", "End Date"}, {"End Date Time.2", "End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each { Number.From ( [Start Date] ) ..Number.From ( [End Date] ) }),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Start Date Time", each if [Start Date] = [Custom] then Text.From ( [Start Date] ) & " " & Text.From ( [Start Time] ) else Text.From ( [End Date] ) & " 00:00"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "End Date Time", each if [End Date] = [Custom] then Text.From ( [End Date] ) & " " & Text.From ( [End Time] ) else Text.From ( [End Date] ) & " 00:00"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "Start Time", "End Date", "End Time", "Custom"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"End Date Time", type datetime}, {"Start Date Time", type datetime}})
in
#"Changed Type2"

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[slorin · Accepted solution]

With Duration.TotalMinutes

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Add_Date = Table.AddColumn(Source, "Date_",
   each List.Dates(
     Date.From([Start Date Time]),
     Duration.Days(Date.From([End Date Time])-Date.From([Start Date Time]))+1,
     #duration(1,0,0,0))),
Date_Expand = Table.ExpandListColumn(Add_Date, "Date_"),
Type_datetime = Table.TransformColumnTypes(Date_Expand,{{"Date_", type datetime}}),
#"Start Date Time 2" = Table.AddColumn(Type_datetime, "Start Date Time 2",
    each List.Max({[Start Date Time],[Date_]})),
#"End Date Time 2" = Table.AddColumn(#"Start Date Time 2", "End Date Time 2",
    each List.Min({[End Date Time],[Date_]+#duration(1,0,0,0)})),
Total_Minutes = Table.AddColumn(#"End Date Time 2", "Total_Minutes",
  each Duration.TotalMinutes([End Date Time 2]-[Start Date Time 2]))
in
Total_Minutes

Stéphane 

[Mahmed1]

Thank you everyone

there are a few solutions here which one is the best and efficient one to use if i have a large data set 

[slorin]

Hi,

another solution

let
Source = YourSource
Add_Date = Table.AddColumn(Source, "Date", 
      each List.Dates(
         Date.From([Start Date Time]),
         Duration.Days(Date.From([End Date Time])-Date.From([Start Date Time]))+1,
         #duration(1,0,0,0))),
Date_Expand = Table.ExpandListColumn(Add_Date, "Date"),
Type_datetime = Table.TransformColumnTypes(Date_Expand,{{"Date", type datetime}}),
#"Start Date Time 2" = Table.AddColumn(Type_datetime, "Start Date Time 2", 
    each List.Max({[Start Date Time],[Date]})),
#"End Date Time 2" = Table.AddColumn(#"Start Date Time 2", "End Date Time 2", 
    each List.Min({[End Date Time],[Date]+#duration(1,0,0,0)}))
in
#"End Date Time 2"

Stéphane 

[Mahmed1]

Thanks to everyone 

it worked beautifully - i trued to add a difference minutes column in power query but it doesnt work when minusing from midnight 

please see image of what its returning and code (it should be 60 minutes for first row)

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Add_Date = Table.AddColumn(Source, "Date_",

      each List.Dates(

         Date.From([Start Date Time]),

         Duration.Days(Date.From([End Date Time])-Date.From([Start Date Time]))+1,

         #duration(1,0,0,0))),

Date_Expand = Table.ExpandListColumn(Add_Date, "Date_"),

Type_datetime = Table.TransformColumnTypes(Date_Expand,{{"Date_", type datetime}}),

#"Start Date Time 2" = Table.AddColumn(Type_datetime, "Start Date Time 2",

    each List.Max({[Start Date Time],[Date_]})),

#"End Date Time 2" = Table.AddColumn(#"Start Date Time 2", "End Date Time 2",

    each List.Min({[End Date Time],[Date_]+#duration(1,0,0,0)})),

    #"Reordered Columns" = Table.ReorderColumns(#"End Date Time 2",{"Date_", "Start Date Time", "End Date Time", "Start Date Time 2", "End Date Time 2"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date_", type date}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Start Date Time 2", "Start_Time"}, {"End Date Time 2", "End_Time"}}),

    #"Inserted Time" = Table.AddColumn(#"Renamed Columns", "Time", each DateTime.Time([Start_Time]), type time),

    #"Inserted Time1" = Table.AddColumn(#"Inserted Time", "Time.1", each DateTime.Time([End_Time]), type time),

    #"Removed Columns" = Table.RemoveColumns(#"Inserted Time1",{"Start Date Time", "End Date Time", "Start_Time", "End_Time"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Time", "Start_Time"}, {"Time.1", "End_Time"}}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Start_Time", type number}, {"End_Time", type number}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Number.Mod([End_Time]-[Start_Time],1)*1440),

    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"End_Time", type time}, {"Start_Time", type time}}),

    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Total_Minutes"}})

in

    #"Renamed Columns2"

thnks

[spinfuzer]

add custom column

Change [Start Date Time] and [End Date Time] to your column names

List.Generate(()=>
    [start = [ Start Date Time], end = Date.From(start) & #time(24,0,0) ],
    (x) => x[end] < [End Date Time] + #duration(1,0,0,0),
    (x) => [ start = x[end], end = Date.From(start) & #time(24,0,0) ],
    (x) => [ start = x[start], end = List.Min({x[end], [End Date Time]}) ]

)

[Mahmed1]

Thank you so much - ill give that a go

Just by looking at the 2nd row - the start date time should be 20/12 as its now from 00:00 till 01:00 so shouldnt say 19/12 for that row

[tackytechtom · Accepted solution]

Hi @Mahmed1 ,

Apologise! 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUNzTSNzJWMDKyMjZV0lEyMoAKGBhaGRgoxepEw4X0FAwsQGJIigzNwIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date Time" = _t, #"End Date Time" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Start Date Time.1", "Start Date Time.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "End Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"End Date Time.1", "End Date Time.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Start Date Time.1", "Start Date"}, {"Start Date Time.2", "Start Time"}, {"End Date Time.1", "End Date"}, {"End Date Time.2", "End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each { Number.From ( [Start Date] ) ..Number.From ( [End Date] ) }),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Start Date Time", each if [Start Date] = [Custom] then Text.From ( [Start Date] ) & " " & Text.From ( [Start Time] ) else Text.From ( [End Date] ) & " 00:00"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "End Date Time", each if [End Date] = [Custom] then Text.From ( [End Date] ) & " " & Text.From ( [End Time] ) else Text.From ( [End Date] ) & " 00:00"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "Start Time", "End Date", "End Time", "Custom"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"End Date Time", type datetime}, {"Start Date Time", type datetime}})
in
#"Changed Type2"

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @Mahmed1,

How about this:

Here the M code that you can paste into the advanced editor. You can check out the steps on the right to get a grasp on what's going on 🙂

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUNzTSNzJWMDKyMjZV0lEyMoAKGBhaGRgoxepEw4X0FAwsQGJIigzNwIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date Time" = _t, #"End Date Time" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Start Date Time.1", "Start Date Time.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "End Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"End Date Time.1", "End Date Time.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Start Date Time.1", "Start Date"}, {"Start Date Time.2", "Start Time"}, {"End Date Time.1", "End Date"}, {"End Date Time.2", "End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each { Number.From ( [Start Date] ) ..Number.From ( [End Date] ) }),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Start Date Time", each if [Start Date] = [Custom] then Text.From ( [Start Date] ) & " " & Text.From ( [Start Time] ) else Text.From ( [Start Date] ) & " 00:00"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "End Date Time", each if [End Date] = [Custom] then Text.From ( [End Date] ) & " " & Text.From ( [End Time] ) else Text.From ( [End Date] ) & " 00:00"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "Start Time", "End Date", "End Time", "Custom"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"End Date Time", type datetime}, {"Start Date Time", type datetime}})
in
#"Changed Type2"

Let me know if this solved the issue, by accepting the answer as the solution. Tag me if you have a question 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/