Skipping overlapping date & get date difference.

[abdsk]

Hi there,

I got a requirement where I need to skip overlapping dates & only consider minimum start date & max end date e.g.

One Machine started at 12-05-2023 02:00 & ended at 12-05-2023 10:30 meanwhile other machine (row no 2) started at 12-05-2023 09:30 & ended at 12-05-2023 16:00 so here 9.30 comes before the earlier machines end time (overlapping) so expected output will be 14 hrs (end time of row no 2 - start time row no 1  i.e. 12-05-2023 16:00 - 12-05-2023 02:00) but the third time when machine goes down (row no 3) is started at 17 (no overlapping) & ends at 20 so it is 3 hrs.
So similarly row no 4 & 5; where start time to be consider as 13-05-2023 10:00 & end time is 13-06-2023 10:00 as the second start date is overlapping.

How to get the output in hrs for such data; your help is much appreciated.

	Downtime
Equipment	Start Time	End Time	Expected Output (hrs)
Machine	12-05-2023 02:00	12-05-2023 10:30	0
Machine	12-05-2023 09:30	12-05-2023 16:00	14
Machine	12-05-2023 17:00	12-05-2023 20:00	3
Machine	13-05-2023 10:00	10-06-2023 00:00	0
Machine	04-06-2023 03:00	13-06-2023 10:00	720	30 days * 24 hrs

[ImkeF · Accepted solution]

Hello @abdsk ,
this can be achieved by the magical 5th parameter in the Table.Group-function.
If you paste the following M-code into the advanced editor of a blank query, you can follow the steps along:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8k1MzsjMS1XSUTI00jcw1TcyMDJWMDCyMjAAIlRRQwMrY7BorA4ufZZQFaj6zKCm4dRnaI7NPiMD7PqMkV0E12egb2AGdQV2fQYmCBXGCH3GcFG4abGxAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Equipment = _t, #"Start Time" = _t, #"End Time" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Start Time", type datetime}, {"End Time", type datetime}}
  ),
  #"Grouped Rows" = Table.Group(
    #"Changed Type",
    {"Equipment", "Start Time", "End Time"},
    {
      {"Count", each _},
      {"MinStart", each List.Min([Start Time]), type nullable datetime},
      {"MaxEnd", each List.Max([End Time]), type nullable datetime}
    },
    GroupKind.Local,
    (x, y) => Number.From(x[End Time] < y[Start Time])
  ),
  #"Inserted Time Subtraction" = Table.AddColumn(
    #"Grouped Rows",
    "Duration",
    each Duration.TotalHours([MaxEnd] - [MinStart])
  )
in
  #"Inserted Time Subtraction"

[ImkeF · Accepted solution]

Hi @abdsk ,
unfortunately this is not so easy any more, as it has a different logic than I originally thought.
Please use this code instead:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdNBDsIwDATAr6Ccg+S4LbS8BKj6/2/UvsGu1hInpJyckRPbyb635+vderNxjeXmfjF7mGXoBqGjV3r5SU9ab6THXerBuSuNx9YaS6o1bpXajbTPWjtrPR3nKnM6LjQeG6FV58Zje5uwlA/NPcnHoG7Ck883KDRtRb9N65W1a72R9v9oKilCs9aYKEK6g/kbUGObvn8D3pv6fZw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, #"Start Time" = _t, #"End Time" = _t]),
ChangedType = Table.TransformColumnTypes(
    Source, 
    {{"Equipment", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}
  ), 
  #"Grouped Rows" = Table.Group(
    ChangedType, 
    {"Equipment"}, 
    {
      {
        "Result", 
        each 
          let
            InputTable = Table.Buffer(Table.SelectColumns(_, {"Start Time", "End Time"})), 
            Custom1 = List.Generate(
              () => [
                MinStart = InputTable{0}[Start Time], 
                MaxEnd   = InputTable{0}[End Time], 
                Group    = 1, 
                Counter  = 0
              ], 
              each [Counter] <= Table.RowCount(InputTable), 
              each [
                CurrentStart = InputTable{[Counter]}[Start Time], 
                CurrentEnd = InputTable{[Counter]}[End Time], 
                GroupChange = CurrentStart > [MaxEnd] and CurrentEnd > [MinStart], 
                MinStart = 
                  if GroupChange then
                    CurrentStart
                  else
                    List.Min({CurrentStart, [MinStart]}), 
                MaxEnd = if GroupChange then CurrentEnd else List.Max({CurrentEnd, [MaxEnd]}), 
                Group = if GroupChange then [Group] + 1 else [Group], 
                Counter = [Counter] + 1
              ]
            ), 
            #"Converted to Table" = Table.FromList(
              Custom1, 
              Splitter.SplitByNothing(), 
              null, 
              null, 
              ExtraValues.Error
            ), 
            #"Expanded Column1" = Table.ExpandRecordColumn(
              #"Converted to Table", 
              "Column1", 
              {"MinStart", "MaxEnd", "Group"}
            ), 
            #"Grouped Rows" = Table.Group(
              #"Expanded Column1", 
              {"Group"}, 
              {
                {"MinStart", each List.Min([MinStart]), type datetime}, 
                {"MaxEnd", each List.Max([MaxEnd]), type datetime}
              }
            )
          in
            #"Grouped Rows"
      }
    }
  ), 
  #"Expanded Result" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "Result", 
    {"MinStart", "MaxEnd"}
  )
in
  #"Expanded Result"

[abdsk]

Thanks again @ImkeF; though difficult to sallow but gives the expected result.

[ImkeF]

Hi @abdsk ,
please give the desired output as a table form and open a new thread for the 2nd topic.
I'm not sure if I have time to look into it, so someone else might pick it up instead.

[abdsk]

Thanks for reply @ImkeF,
here is the expected output

Equipment	MinStart	MaxEnd
XYZ	01-01-2022 00:00:00	06-01-2022 00:00:00
XYZ	09-01-2022 00:00:00	17-01-2022 00:00:00
XYZ	20-01-2022 00:00:00	20-02-2022 00:00:00

[abdsk]

Hey Hi @ImkeF ,

The above logic is working for most of the data but failing in some scenarios.

PFB PQ FYR 

=========================================

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdNBDsIwDATAr6Ccg+S4LbS8BKj6/2/UvsGu1hInpJyckRPbyb635+vderNxjeXmfjF7mGXoBqGjV3r5SU9ab6THXerBuSuNx9YaS6o1bpXajbTPWjtrPR3nKnM6LjQeG6FV58Zje5uwlA/NPcnHoG7Ck883KDRtRb9N65W1a72R9v9oKilCs9aYKEK6g/kbUGObvn8D3pv6fZw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, #"Start Time" = _t, #"End Time" = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Equipment", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}),

  #"Grouped Rows" = Table.Group(

    #"Changed Type",

    {"Equipment", "Start Time", "End Time"},

    {

      {"Count", each _},

      {"MinStart", each List.Min([Start Time]), type nullable datetime},

      {"MaxEnd", each List.Max([End Time]), type nullable datetime}

    },

    GroupKind.Local,

    (x, y) => Number.From(x[End Time] < y[Start Time])

  )

in

    #"Grouped Rows"

=========================================

Here the output should be 3 rows, the last row should be

20-1-2023 & 20-2-2023

as the other dates are coming in between.

I have another requirement where I need to calculate the uptime (above is downtime)

Data is,

Equipment Date Reading (Hrs)

XYZ 07-01-2022 24345

XYZ 12-03-2022 53464

Here the output should be 29119 (53464 - 24345)

but the output should be grouped based on the month

so, 29119 should be divided by number of days available between downtime dates

e.g. downtime was from 01-01-2022 to 06-01-2022 then 9-1-2022 to 17-01-2022 and finally from 20-1-2022 to 20-2-2022

so in the Jan we have only 4 days (7, 8, 18, 19, rest other day it was downtime)

Feb we have 8 days (as downtime till 20-2-2022)

March its 12 days

so total is 29119/24hrs * 24 days (4+8+12 days) = 50.55381944 per day

so final output should be,

Jan 4 202.2152778 (4* 50.55381944)

Feb 8 404.4305556

Mar 12 606.6458333

Your help is much appreciated

[abdsk]

Thank you so much @ImkeF, this is really next level; really appreciate the help.

[ImkeF · Accepted solution]

Hello @abdsk ,
this can be achieved by the magical 5th parameter in the Table.Group-function.
If you paste the following M-code into the advanced editor of a blank query, you can follow the steps along:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8k1MzsjMS1XSUTI00jcw1TcyMDJWMDCyMjAAIlRRQwMrY7BorA4ufZZQFaj6zKCm4dRnaI7NPiMD7PqMkV0E12egb2AGdQV2fQYmCBXGCH3GcFG4abGxAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Equipment = _t, #"Start Time" = _t, #"End Time" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Start Time", type datetime}, {"End Time", type datetime}}
  ),
  #"Grouped Rows" = Table.Group(
    #"Changed Type",
    {"Equipment", "Start Time", "End Time"},
    {
      {"Count", each _},
      {"MinStart", each List.Min([Start Time]), type nullable datetime},
      {"MaxEnd", each List.Max([End Time]), type nullable datetime}
    },
    GroupKind.Local,
    (x, y) => Number.From(x[End Time] < y[Start Time])
  ),
  #"Inserted Time Subtraction" = Table.AddColumn(
    #"Grouped Rows",
    "Duration",
    each Duration.TotalHours([MaxEnd] - [MinStart])
  )
in
  #"Inserted Time Subtraction"