Sum workload per maximum Timestamp per caseID

[niveekiwi]

I need to calculate Sumworkload, per ID, for the maximum timestamp per day, per assignment group. Sum workload is the result I would like to have.

ID	Timestamp	Assignment Group	Workload	Activity	Sumworkload
a	21-1-2020 21:00	abce	1	open	-2
a	21-1-2020 21:01	abce	0	in progress	-2
a	21-1-2020 21:01	abde	-1	reassigned	-2
a	21-1-2020 21:01	abce	-1	reassigned	-2
b	22-1-2020 00:00	abvn	1	assigned	1
a	22-1-2020 09:00	abcd	1	assigned	1

[Anonymous · Accepted solution]

split the time_stamp column in two columns "day" and "stamp" then try this:

rr = Table.Group(youTab, {"id", "day"}, {{"sum", (s)=> List.Sum(Table.SelectRows(s, each [stamp]=List.Max(s[stamp]))[work])},{"cols", each _}}),
te= Table.ExpandTableColumn(yourTab, "cols", {"stamp", "group", "work"}, {"stamp", "group", "work"})
in
te

[v-juanli-msft · Accepted solution]

Hi @niveekiwi 

As tested, Rocco_sprmnt21's answer works.

To add "per assignment group" in your conditions, please modify the query as below and paste in your Advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdDBCoAgDADQXwnPCdtu9SviQXNIFxWFvj+Fyg5FHcbG2GNsSgkjRkEoURIQDIQzQO0Yu3BNWCMmDjVJEnp8Gsc+3uQahpSjz1zKt3JNyVZnNqWsPrD7uetV2aboVADnQVs4DroR7Hu6mK4XuEehdw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Timestamp = _t, #"Assignment Group" = _t, Workload = _t, Activity = _t, Sumworkload = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Timestamp", type text}, {"Assignment Group", type text}, {"Workload", Int64.Type}, {"Activity", type text}, {"Sumworkload", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Timestamp", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"day", "stamp"}),
    rr = Table.Group(#"Split Column by Delimiter", {"ID", "day","Assignment Group"}, {{"sum", (s)=> List.Sum(Table.SelectRows(s, each [stamp]=List.Max(s[stamp]))[Workload])},{"cols", each _}}),
    #"Grouped Rows" = Table.Group(rr, {"ID", "day"}, {{"sum2", each List.Sum([sum]), type number}, {"all", each _, type table [ID=text, day=text, Assignment Group=text, sum=number, cols=table]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"cols"}, {"all.cols"}),
    #"Expanded all.cols" = Table.ExpandTableColumn(#"Expanded all", "all.cols", {"stamp", "Assignment Group", "Workload", "Activity"}, {"all.cols.stamp", "all.cols.Assignment Group", "all.cols.Workload", "all.cols.Activity"})
in
    #"Expanded all.cols"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

split the time_stamp column in two columns "day" and "stamp" then try this:

rr = Table.Group(youTab, {"id", "day"}, {{"sum", (s)=> List.Sum(Table.SelectRows(s, each [stamp]=List.Max(s[stamp]))[work])},{"cols", each _}}),
te= Table.ExpandTableColumn(yourTab, "cols", {"stamp", "group", "work"}, {"stamp", "group", "work"})
in
te