Extract 24 hour date/time ranges as additional rows

[Lucas01]

Good day,

I am encountering an issue which I cannot figure out by myself. Hopefully one of you can help me 😀

From an excel file, I extract tasks and their respective start- and end date/time columns (see example below). These can range from times within the same day, but also span multiple days. However, for each task I need to have the time span per day in which the task is “active” ranging from 00:00:00 until 23:59:59 (see example for better understanding).

TABLE1 (start situation)

Task	Start Date/Time	End Date/Time
1	14/09/2020 14:00:00	14/09/2020 16:00:00
2	14/09/2020 14:00:00	15/09/2020 16:00:00
3	14/09/2020 14:00:00	18/09/2020 16:00:00

Table 1: (needed situation)

Task	Start Date/Time	End Date/Time
1	14/09/2020 14:00:00	14/09/2020 16:00:00
2	14/09/2020 14:00:00	14/09/2020 23:59:59
2	15/09/2020 00:00:00	15/09/2020 16:00:00
3	14/09/2020 14:00:00	14/09/2020 23:59:59
3	15/09/2020 00:00:00	15/09/2020 23:59:59
3	16/09/2020 00:00:00	16/09/2020 23:59:59
3	17/09/2020 00:00:00	17/09/2020 23:59:59
3	18/09/2020 00:00:00	18/09/2020 16:00:00

I currently do this with a function but it takes forever (i.e. hours) to load it like this and the loading window shows a much higher number of MBs than the size of the actual Excel file. In the end, I end up with a table of appr. 900K lines.

Thank you in advance!

[Adalro]

Hi @v-alq-msft ,

I have a similar problem. But your solution is not working correctly for me. Here is an excerpt:

He gets the days right but the times are not correct.

Is there a solution for this?

Thx!

[v-alq-msft · Accepted solution]

Hi, @Lucas01 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may paste the following m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbLUNzTRNzIwMlAwNLEyMAAiVEEzqGCsTrSSER71pljUG+NRb4GuPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, #"Start Date/Time" = _t, #"End Date/Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", Int64.Type}, {"Start Date/Time", type datetime}, {"End Date/Time", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Re", each let startdate=Date.From([#"Start Date/Time"]),
enddate=Date.From([#"End Date/Time"])
in
List.Generate(
    ()=>[s=startdate,e=startdate],
    each [s]<=enddate,
    each [s=[s]+#duration(1,0,0,0),e=[e]+#duration(1,0,0,0)]
)),
    #"Expanded Re" = Table.ExpandListColumn(#"Added Custom1", "Re"),
    #"Expanded Re1" = Table.ExpandRecordColumn(#"Expanded Re", "Re", {"s", "e"}, {"Re.s", "Re.e"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Re1", "NewStart", each let date=[Re.s],time=Time.From([#"Start Date/Time"]),
task=[Task],
tab = Table.SelectRows(#"Expanded Re1",each [Task]=task),
mindate=Table.Min(tab,"Re.s")[Re.s]
in 
if [Re.e]=mindate
then
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    Time.Hour(time),
    Time.Minute(time),
    Time.Second(time)
)
else 
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    0,
    0,
    0
)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "NewEnd", each let date=[Re.e],time=Time.From([#"End Date/Time"]),
task=[Task],
tab = Table.SelectRows(#"Expanded Re1",each [Task]=task),
mindate=Table.Max(tab,"Re.e")[Re.e]
in 
if [Re.e]=mindate
then
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    Time.Hour(time),
    Time.Minute(time),
    Time.Second(time)
)
else 
#datetime(
    Date.Year(date),
    Date.Month(date),
    Date.Day(date),
    23,
    59,
    59
)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date/Time", "End Date/Time", "Re.s", "Re.e"})
in
    #"Removed Columns"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samdthompson]

Hello, here is a solution. first the end result, then how I chose to get there. Always more than one solution but this one works for me:

you can use this code to achieve it. Obviously you will need to alter the #Source line:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(
      Source, 
      {{"Task", Int64.Type}, {"Start Date/Time", type datetime}, {"End Date/Time", type datetime}}
    ),
  #"Added Custom" = Table.AddColumn(
      #"Changed Type", 
      "Diff", 
      each [#"End Date/Time"] - [#"Start Date/Time"]
    ),
  #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Diff", "Diff - Copy"),
  #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column", {{"Diff - Copy", Int64.Type}}),
  #"Added Custom1" = Table.AddColumn(
      #"Changed Type1", 
      "Custom", 
      each Text.Repeat("1", [#"Diff - Copy"])
    ),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom1", {"Diff - Copy"}),
  #"Split Column by Position" = Table.SplitColumn(
      #"Removed Columns", 
      "Custom", 
      Splitter.SplitTextByRepeatedLengths(1), 
      {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}
    ),
  #"Changed Type2" = Table.TransformColumnTypes(
      #"Split Column by Position", 
      {
        {"Custom.1", Int64.Type}, 
        {"Custom.2", Int64.Type}, 
        {"Custom.3", Int64.Type}, 
        {"Custom.4", Int64.Type}
      }
    ),
  #"Added Custom2" = Table.AddColumn(
      #"Changed Type2", 
      "Custom", 
      each if [Custom.1] = null then 1 else [Custom.1]
    ),
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
      #"Added Custom2", 
      {"Task", "Start Date/Time", "End Date/Time", "Diff"}, 
      "Attribute", 
      "Value"
    ),
  #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns", {"Attribute"}),
  #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns1", "Diff", "Diff - Copy"),
  #"Changed Type3" = Table.TransformColumnTypes(
      #"Duplicated Column1", 
      {{"Diff - Copy", Int64.Type}}
    ),
  #"Added Index" = Table.AddIndexColumn(#"Changed Type3", "Index", 0, 1),
  #"Added Custom3" = Table.AddColumn(
      #"Added Index", 
      "Custom", 
      each ([Index] - ([#"Diff - Copy"] - [Task])) - 1
    ),
  #"Added Custom4" = Table.AddColumn(
      #"Added Custom3", 
      "End Date/Time_altered", 
      each 
        if [Custom] = 0 then 
          [#"Start Date/Time"]
        else if [Custom] <= [#"Diff - Copy"] then 
          (Date.EndOfDay(
              Date.AddDays([#"Start Date/Time"], (Number.Abs([#"Diff - Copy"] - [Custom])))
            ))
            - #duration(0, 0, 0, 1)
        else 
          [#"End Date/Time"]
    ),
  #"Changed Type4" = Table.TransformColumnTypes(
      #"Added Custom4", 
      {{"End Date/Time_altered", type datetime}}
    ),
  #"Removed Columns2" = Table.RemoveColumns(
      #"Changed Type4", 
      {"Diff", "Value", "Diff - Copy", "Index", "Custom", "End Date/Time"}
    ),
  #"Sorted Rows" = Table.Sort(
      #"Removed Columns2", 
      {{"Task", Order.Ascending}, {"End Date/Time_altered", Order.Ascending}}
    )
in
  #"Sorted Rows"

[AlB]

Hi @Lucas01 

Can u share

1. the original excel file, so that we can work on  the real thing for optimization?

2.  what you have done so far (i.e., the code of the function and anything else of relevance)?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Jimmy801]

Hello @Lucas01 

check out this solution. Uses List.Dates to generate a list and List.Generate to create a table with your new start end based on the dateslist. Would be curious if this is now any faster

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00Tew1DcyMDJQMDSxMjAAIjRRM6horE60khFuHabYdRjj1mGBRUcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, #"Start Date/Time" = _t, #"End Date/Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", Int64.Type}, {"Start Date/Time", type datetime,"it-IT"}, {"End Date/Time", type datetime, "it-IT"}}),
    AddTimeList = Table.AddColumn
    (
        #"Changed Type",
        "TimeRecord",
        (add)=> 
        let  
            Sameday =  Date.From(add[#"Start Date/Time"])= Date.From(add[#"End Date/Time"]), 
            CreateDateList = if Sameday = false then List.Dates(Date.From(add[#"Start Date/Time"]),Duration.Days(Date.From(add[#"End Date/Time"])-Date.From(add[#"Start Date/Time"]))+1,#duration(1,0,0,0)) else {""},
            GenerateRecords = List.Generate
            (
                ()=> [Start = CreateDateList{0}  & Time.From(add[#"Start Date/Time"]), End = Date.From(add[#"Start Date/Time"]) & Time.From("23:59:59"), Counter = 1],
                each [Counter]<= List.Count(CreateDateList),
                each if List.Count(CreateDateList) = [Counter] +1 then [Start = CreateDateList{[Counter]} & Time.From("0:00:00"), End = Date.From(add[#"Start Date/Time"]) & Time.From(add[#"End Date/Time"]), Counter = [Counter]+1]  else [Start = CreateDateList{[Counter]} & Time.From("0:00:00"), End = Date.From(add[#"Start Date/Time"]) & Time.From("23:59:59"), Counter = [Counter]+1],
                each [Start= Record.Field(_, "Start"), End= Record.Field(_,"End")]
            )
        in 
            if Sameday= true then Table.FromRecords( {[Start= add[#"Start Date/Time"], End= add[#"End Date/Time"] ]}) else Table.FromRecords(GenerateRecords)
    ),
    #"Expanded TimeRecord" = Table.ExpandTableColumn(AddTimeList, "TimeRecord", {"Start", "End"}, {"Start", "End"})
in
    #"Expanded TimeRecord"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy