Forum Discussion

Lucas01's avatar
Lucas01
New Member
5 years ago
Solved

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...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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's avatar
samdthompson
Memorable Member
5 years ago

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"