Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lucas01
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
v-alq-msft
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

5 REPLIES 5
Adalro
Frequent Visitor

Hi @v-alq-msft ,

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

datenset_test.png

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

 

Is there a solution for this?

 

Thx!

v-alq-msft
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.

 

samdthompson
Memorable Member
Memorable Member

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.
AlB
Super User
Super User

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

Jimmy801
Community Champion
Community Champion

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors