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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors