Summary Table

[Anonymous]

Hi

I have a table with the following data:

Hours        Serial Number       Failures

20                       A                        0

15                       B                        0

40                       A                        1

30                       A                        0

50                       A                        1

How do I create a summary table that lists the total hours for A until the first failure and hours after.  If there aren't any failures then, report the total hours in for that serial number.  In the above example, I would need a table that shows the following:

Serial Number              Total Hours

A                                          60

A                                          80

B                                          15

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous 

Try this Power Query /M solution

Please see Query Editor of attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQ0lFyBGIDpVidaCVDUyDTCc41gckagrnGqIpNkWRjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Hours = _t, #"Serial Number" = _t, Failures = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hours", Int64.Type}, {"Serial Number", type text}, {"Failures", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let myindex=[Index] in let mylist=
List.Max(Table.SelectRows(#"Added Index",each [Index]<myindex and [Failures]=1)[Index])+1
in
if mylist=null then 1 else mylist),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Serial Number", "Custom"}, {{"Sum", each List.Sum([Hours]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Custom"})
in
    #"Removed Columns"