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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
64 | |
61 | |
23 | |
17 | |
12 |