Split data into right date

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 

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/

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

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

)

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