March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Solved! Go to 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"
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
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
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
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
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 |
Thank you so much @ImkeF, this is really next level; really appreciate the help.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
16 | |
13 | |
13 | |
9 |
User | Count |
---|---|
36 | |
31 | |
20 | |
19 | |
17 |