I want to calculate the top 3 consecutive outages

[Anonymous]

The base data looks like this 

Creation Date	Time	Site Name (CRM adress 3)
16/05/2018	1	X
16/05/2018	2	X
16/05/2018	3	X
16/05/2018	4	X
16/05/2018	5	X
16/05/2018	13	X
16/05/2018	14	X
16/05/2018	15	X
16/05/2018	16	X
01/05/2018	1	Y
06/05/2018	2	Y
07/05/2018	3	Y
08/05/2018	4	Y
12/05/2018	3	Y
13/05/2018	2	Y
13/05/2018	4	Y
14/05/2018	2	Y
15/05/2018	3	Y
20/05/2018	4	Y
24/05/2018	3	Y
24/05/2018	4	Y
25/05/2018	3	Y
30/05/2018	4	Y
16/05/2018	1	Z
18/05/2018	3	Z
19/05/2018	3	Z
23/05/2018	3	Z
28/05/2018	3	Z

I want to calculate top 3 consecutive outages by consecutive hours on any single day

The Base data looks like this

Creation Date	Time	Site Name (CRM adress 3)
16/05/2018	1	X
16/05/2018	2	X
16/05/2018	3	X
16/05/2018	4	X
16/05/2018	5	X
16/05/2018	13	X
16/05/2018	14	X
16/05/2018	15	X
16/05/2018	16	X
01/05/2018	1	Y
06/05/2018	2	Y
07/05/2018	3	Y
08/05/2018	4	Y
12/05/2018	3	Y
13/05/2018	2	Y
13/05/2018	4	Y
14/05/2018	2	Y
15/05/2018	3	Y
20/05/2018	4	Y
24/05/2018	3	Y
24/05/2018	4	Y
25/05/2018	3	Y
30/05/2018	4	Y
16/05/2018	1	Z
18/05/2018	3	Z
19/05/2018	3	Z
23/05/2018	3	Z
28/05/2018	3	Z

I want to caluclate the list of top 3 outages

The result should look like this

Site	Date	Number of Cosecutive Hours Missing	Hours missing
X	16-May-18	5	Between 1 am and 5 am
X	16-May-18	4	Between 13 pm to 16 pm
Y	24-May-18	2	Between 3 am and 4 am

 
How can I achieve this using Power BI?

Rgds

Amit

[AlexisOlson]

I've created a query that should do the job. Here's the M code to reproduce (just paste into your Advanced Editor).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJBCoAgEIXhu7gW8o2j2U0q8f7XKFCipufCzcfwwwNrdchLSIsEFOcd7re75g0L58hZOSfOmFQwyWDWyY8HmElHZztp8GomDS5mUmcIvUak7Q+/IsqvE21LoBFRfq38mrcjb/8+xdm5mMjgjbJEziTSLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Creation Date" = _t, Time = _t, #"Site Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", Int64.Type}, {"Site Name", type text}, {"Creation Date", type text}}),
    #"Added Boole Column" = Table.AddColumn(#"Changed Type", "Boole",
                                (C) => List.Contains(Table.SelectRows(#"Changed Type",
                                           each C[Creation Date] = [Creation Date] and C[Site Name] = [Site Name])[Time], C[Time] + 1), type logical),
    #"Added Grouping Column" = Table.AddColumn(#"Added Boole Column", "Group",
                                   each Table.RowCount(Table.SelectRows(#"Added Boole Column",
                                   (C) => C[Creation Date] = [Creation Date] and C[Time] < [Time] and C[Site Name] = [Site Name] and C[Boole] = false)), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Grouping Column",
                          {"Creation Date", "Site Name", "Group"},
                          {{"Count", each Table.RowCount(_), type number}, {"Min", each List.Min([Time]), type number}, {"Max", each List.Max([Time]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Group"}),
    #"Kept First 3 Rows" = Table.FirstN(#"Removed Columns",3)
in
    #"Kept First 3 Rows"

The first step is to calculate a Boolean (True/False) column which determines whether the current hour is contiguous with the next hour. With that in hand, I group the outages into contiguous pieces by counting the FALSE values prior to the current time on that day. Using the Group column I can group by date, site, and group using the count aggregator to get the number of hours and the max and min to get the endpoints.