Forum Discussion
Solved
Group by and conditional column
Hello all, Been killing myself on this one and cant figure it out. So I have a table with call records that I am grouping by number, time and date into a column called "call routing". I then u...
Hi, danish169
M code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vBR0lEyNNE3NALRBlbGBkAajA2NTE2BtCUQJ+WX5iWnKsXqRCs5BZOm3tcFi3pDI3Qdnn7OYOVB7qQZ71GaV6KQXpRfWqCQVFpJhF5P/VCwxgwUjQpJicnZpQVE6Q8B608sLclXSCwpSc1LScwrIdHR4DA3hqo3sTJAVg8WM8UW6CRogIQ6sgaQagU8OiABT4IVmCFPgmacoU/IDFj0YQY/CbZD/GoE0WBgCdEAVgTSAeKA2P6hwJiOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user = _t, date = _t, time = _t, duration = _t, number = _t, #"ring time" = _t, #"type" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"user", type text}, {"date", type text}, {"time", type text}, {"duration", Int64.Type}, {"number", Int64.Type}, {"ring time", Int64.Type}, {"type", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "time", "number"}, {{"Call Routing", each _, type table [user=nullable text, date=nullable text, time=nullable text, duration=nullable number, number=nullable number, ring time=nullable number, type=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hunt Froup Name", each Table.SelectRows([Call Routing], each ([type] = "I/T" or [type] = "I/U"))), #"Aggregated Custom" = Table.AggregateTableColumn(#"Added Custom", "Hunt Froup Name", {{"user", List.Min, "Hunt Froup Name"}}), #"Added Custom1" = Table.AddColumn(#"Aggregated Custom", "Staff Member", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))), #"Aggregated Staff Member" = Table.AggregateTableColumn(#"Added Custom1", "Staff Member", {{"user", List.Min, "Staff Member"}}), #"Added Custom2" = Table.AddColumn(#"Aggregated Staff Member", "duration", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))), #"Aggregated duration" = Table.AggregateTableColumn(#"Added Custom2", "duration", {{"duration", List.Min, "duration"}}), #"Added Custom3" = Table.AddColumn(#"Aggregated duration", "ring time", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))), #"Aggregated ring time" = Table.AggregateTableColumn(#"Added Custom3", "ring time", {{"ring time", List.Min, "ring time"}}) in #"Aggregated ring time"Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, danish169
M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vBR0lEyNNE3NALRBlbGBkAajA2NTE2BtCUQJ+WX5iWnKsXqRCs5BZOm3tcFi3pDI3Qdnn7OYOVB7qQZ71GaV6KQXpRfWqCQVFpJhF5P/VCwxgwUjQpJicnZpQVE6Q8B608sLclXSCwpSc1LScwrIdHR4DA3hqo3sTJAVg8WM8UW6CRogIQ6sgaQagU8OiABT4IVmCFPgmacoU/IDFj0YQY/CbZD/GoE0WBgCdEAVgTSAeKA2P6hwJiOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user = _t, date = _t, time = _t, duration = _t, number = _t, #"ring time" = _t, #"type" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"user", type text}, {"date", type text}, {"time", type text}, {"duration", Int64.Type}, {"number", Int64.Type}, {"ring time", Int64.Type}, {"type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"date", "time", "number"}, {{"Call Routing", each _, type table [user=nullable text, date=nullable text, time=nullable text, duration=nullable number, number=nullable number, ring time=nullable number, type=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hunt Froup Name", each Table.SelectRows([Call Routing], each ([type] = "I/T" or [type] = "I/U"))),
#"Aggregated Custom" = Table.AggregateTableColumn(#"Added Custom", "Hunt Froup Name", {{"user", List.Min, "Hunt Froup Name"}}),
#"Added Custom1" = Table.AddColumn(#"Aggregated Custom", "Staff Member", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))),
#"Aggregated Staff Member" = Table.AggregateTableColumn(#"Added Custom1", "Staff Member", {{"user", List.Min, "Staff Member"}}),
#"Added Custom2" = Table.AddColumn(#"Aggregated Staff Member", "duration", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))),
#"Aggregated duration" = Table.AggregateTableColumn(#"Added Custom2", "duration", {{"duration", List.Min, "duration"}}),
#"Added Custom3" = Table.AddColumn(#"Aggregated duration", "ring time", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))),
#"Aggregated ring time" = Table.AggregateTableColumn(#"Added Custom3", "ring time", {{"ring time", List.Min, "ring time"}})
in
#"Aggregated ring time"Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please can I request assistance in tweaking this solution as my data has slightly changed and causing issues.
Firstly,
I need to restrict the options to populate "Staff Member" and "Hunt Group Name"
For example staff member can only be "Sarah", "Roger", Alan" else leave it null
Hunt group name can only be "sales", "order update" else leave it null
Secondly,
I also need to add a new column to the end called "Divert" that populates with "Receptionist" if the number field in the grouped table = "0199666" else leave it null.
Thanks in advance.
Hi, danish169
Try this:M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vBR0lEyNNE3NALRBlbGBkAajA2NTE2BtCUQJ+WX5iWnKsXqRCs5BZOm3tcFi3pDI3Qdnn7OYOVB7qQZ71GaV6KQXpRfWqCQVFpJhF5P/VCwxgwUjQpJicnZpQVE6Q8B608sLclXSCwpSc1LScwrIdHR4DA3hqo3sTJAVg8WM8UW6CRogIQ6mgYFPBog4U6CDZgBT4JmnIFPyAxY7GGGPgm2Q/xqBNFgYAnRAFYE0gHigNj+ocCIjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user = _t, date = _t, time = _t, duration = _t, number = _t, #"ring time" = _t, #"type" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"user", type text}, {"date", type text}, {"time", type text}, {"duration", Int64.Type}, {"number", Int64.Type}, {"ring time", Int64.Type}, {"type", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "time", "number"}, {{"Call Routing", each _, type table [user=nullable text, date=nullable text, time=nullable text, duration=nullable number, number=nullable number, ring time=nullable number, type=nullable text]}}), #"Added Hunt Froup Name" = Table.AddColumn(#"Grouped Rows", "Hunt Froup Name", each Table.SelectRows([Call Routing], each ([type] = "I/T" or [type] = "I/U") and (List.PositionOf( {"sales","order update","Hunt group buy"},//the list you want [user])<>-1) )), #"Aggregated Hunt Froup Name" = Table.AggregateTableColumn(#"Added Hunt Froup Name", "Hunt Froup Name", {{"user", List.Min, "Hunt Froup Name"}}), #"Added Staff Member" = Table.AddColumn(#"Aggregated Hunt Froup Name", "Staff Member", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT")and (List.PositionOf( {"Sarah", "Roger", "Alan","RG"},// the list you want [user])<>-1) )), #"Aggregated Staff Member" = Table.AggregateTableColumn(#"Added Staff Member", "Staff Member", {{"user", List.Min, "Staff Member"}}), #"Added duration" = Table.AddColumn(#"Aggregated Staff Member", "duration", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))), #"Aggregated duration" = Table.AggregateTableColumn(#"Added duration", "duration", {{"duration", List.Min, "duration"}}), #"Added ring time" = Table.AddColumn(#"Aggregated duration", "ring time", each Table.SelectRows([Call Routing], each ([type] = "INC" or [type] = "OUT"))), #"Aggregated ring time" = Table.AggregateTableColumn(#"Added ring time", "ring time", {{"ring time", List.Min, "ring time"}}), #"Added Divert" = Table.AddColumn(#"Aggregated ring time", "Divert", each if List.PositionOf( {1252},// the number you want [number])<>-1 then "Receptionist" else null) in #"Added Divert"Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.