Sum time per category

[Anonymous]

Hello,

I am stuck with a problem where i have to sum times per different categories. My Data looks roughly like this:

What i need is to sum the hours by "Date" and "User" and between the "outward run" and "Return run" in the type-column, to get the Data like this:

To achive this i think i need a column somewhat like that:

With that i could group the Data by "Date", "User" and "Helper". Yet i do not know how to get that Helper-column.

Can anyone help me?

Thanks in advance.

Regards,

Dennis

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

Please check the applied steps in query editor to get the helper column.Attached the file for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI30TcyMLRU0lFyBGL/0pLyxKIUhaLSPCDPAohBMoZKsToYSsPzi7Iz89IVSjJzU2HqDIGEETbFQaklpUV5UGPBygyNiDQYotAEl8moLgarMzTDpRjdaJBCsKQxYUeDRI0QHjRGqHUiEHBoSvEHHH7F6EFHQDVYoTGxqo1hQWKMTTVqaIBDzhxiciwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, User = _t, Type = _t, Start = _t, End = _t, Hour = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User", type text}, {"Type", type text}, {"Start", Int64.Type}, {"End", Int64.Type}, {"Hour", Int64.Type}}),
    Partition = Table.Group( #"Changed Type", {"Type"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "User", "Start", "End", "Hour", "Index"}, {"Date", "User", "Start", "End", "Hour", "Index"}),

    #"Sorted Rows" = Table.Sort(#"Expanded Partition1",{{"Date", Order.Ascending}, {"Start", Order.Ascending}}),

    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Outward_Index", each if [Type] = "Outward run" then [Index] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column", "Return_Index", each if [Type] = "Return run" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column2",{"Outward_Index"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Return_Index"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Up", "Custom", each if [Outward_Index] <> [Return_Index] then [Outward_Index]+[Return_Index] else [Outward_Index]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Helper"}})
in
    #"Renamed Columns"

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft ,

Thanks alot for your help. That is exactly what i was looking for 🙂

Regards,

Dennis