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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
abdsk
New Member

Skipping overlapping date & get date difference.

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  
EquipmentStart TimeEnd TimeExpected Output (hrs) 
Machine12-05-2023 02:0012-05-2023 10:300 
Machine12-05-2023 09:3012-05-2023 16:0014 
Machine12-05-2023 17:0012-05-2023 20:003 
Machine13-05-2023 10:0010-06-2023 00:000 
Machine04-06-2023 03:0013-06-2023 10:0072030 days * 24 hrs
2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

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"

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF
Super User
Super User

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
ImkeF
Super User
Super User

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks for reply @ImkeF,
here is the expected output

EquipmentMinStartMaxEnd
XYZ01-01-2022 00:00:0006-01-2022 00:00:00
XYZ09-01-2022 00:00:0017-01-2022 00:00:00
XYZ20-01-2022 00:00:0020-02-2022 00:00:00
abdsk
New Member

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
New Member

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

ImkeF
Super User
Super User

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"

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors