cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Extract 24 hour date/time ranges as additional rows

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)

TaskStart Date/TimeEnd Date/Time
114/09/2020 14:00:0014/09/2020 16:00:00
214/09/2020 14:00:0015/09/2020 16:00:00
314/09/2020 14:00:0018/09/2020 16:00:00

 

Table 1: (needed situation)

TaskStart Date/TimeEnd Date/Time
114/09/2020 14:00:0014/09/2020 16:00:00
214/09/2020 14:00:0014/09/2020 23:59:59
215/09/2020 00:00:0015/09/2020 16:00:00
314/09/2020 14:00:0014/09/2020 23:59:59
315/09/2020 00:00:0015/09/2020 23:59:59
316/09/2020 00:00:0016/09/2020 23:59:59
317/09/2020 00:00:0017/09/2020 23:59:59
318/09/2020 00:00:0018/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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi, @Lucas01 

 

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

Table:

a1.png

 

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"

 

a2.png

 

Result:

a3.png

 

Best Regards

Allan

 

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

 

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

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"

 

Jimmy801_0-1600106992553.png

 

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

 

Highlighted
Super User II
Super User II

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 

 

SU18_powerbi_badge

Highlighted
Impactful Individual
Impactful Individual

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:

 

2020-09-15_9-28-51.jpg

 

 

 

 

 

 

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"

 

 

 

// if this is a solution please mark as such. Kudos always appreciated.
Highlighted
Community Support
Community Support

Hi, @Lucas01 

 

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

Table:

a1.png

 

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"

 

a2.png

 

Result:

a3.png

 

Best Regards

Allan

 

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

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors