Combining rows and sum times

[Gstewey]

I'd like to combine the values for each machine.   Here I have the machines listed several times, with distint values (time) for planned, running, idle, change over, unplanned, offline.    

I'd like to combine all the times from planned - offline into one column by machine name.   

So each machine name would be listed once, with the sums of each other column together.

[Gstewey]

[Anonymous · Accepted solution]

Hi @Gstewey ,

Please refer to:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HY27FUAhCEN3obYQED9ZxWOh+w/xwqtIQrjsLfdKEV/oiEXVhw9Ygw8a44Aa1VjwCW2UwQQt16iocsqW9+jmZD3Q/s6qhkBeRkU4NJKNmLD8YgrSEqEM5ZwP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Machine name" = _t, Running = _t, Planned = _t, Idle = _t, #"Change Over" = _t, Unplanned = _t, Offline = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Machine name"}, "Attribute", "Value"),
    Custom1 = Table.TransformColumns(#"Unpivoted Columns", {{"Value", each let 
    padded = Text.PadStart(_, 8, "0"),
    parsed = Text.Split(padded, ":") 
    in #duration(0, Int64.From(parsed{0}), Int64.From(parsed{1}), Int64.From(parsed{2})), type duration}}),
    #"Grouped Rows" = Table.Group(Custom1, {"Machine name"}, {{"Duration Total", each List.Sum([Value]), type duration}})
in
    #"Grouped Rows"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum