Forum Discussion
Group by and conditional column
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
Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented? And It would be great if there is a sample file without any sesentive information here. It makes it easier to give you a solution.
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,
Sure - I will put some test date here.... showing a call on three separate days. The call on the 14th was answered (type = INC), the call on the 13th missed entirely (type = bounced) and the one on the 12th was outgoing (type = OUT")
Original data set looks something like the following:user date time duration number ring time type HL 14/12 10:30 0 01255 9 bounce BS 14/12 10:30 0 01255 9 bounce MD 14/12 10:30 120 01255 9 INC RG 14/12 10:30 0 01255 9 bounce Hunt group buy 14/12 10:30 0 01255 9 I/U hunt group buy backup 14/12 10:30 0 01255 9 I/T auto attendant 14/12 10:30 0 01255 9 bounce HL 13/12 14:00 0 01252 15 bounce BS 13/12 14:00 0 01252 15 bounce MD 13/12 14:000 01252 15 bounce RG 13/12 14:00 0 01252 15 bounce Hunt group buy 13/12 14:00 0 01252 15 bounce hunt group buy backup 13/12 14:00 0 01255 15 I/U auto attendant 13/12 14:00 0 01252 15 bounce RG 12/12 09:00 15 01250 5 OUT
I would like to use a group by to group a single call by number, time and date in "call routing" - in this grouping step is where I try to create two new columns called "Hunt Group Name" and "Staff Member".
After grouping the user column becomes superfluous in the final data yet is important in getting the next steps done....
Steps:
1. If a row has type = "I/T" or "I/U" I need to add this user to "Hunt Group Name"
2. If a row has type = "INC" or type = "OUT" add the user to "Staff Member"
3. When type = "INC" or type = "OUT" use that row to populate duration and ring time (there is only ever one instance per group).
4. Set type in this priority order - INC, OUT then bounce.
I am hoping to get the dataset to look like this for the three days.date time duration number ring time type call routing (group) Hunt Group Name Staff Member 14/12 10:30 120 01255 9 INC Table Hunt group buy MD 13/12 14:00 null 01252 null bounce Table hunt group buy backup null 12/12 09:00 15 01250 5 OUT Table null RG
Hopefully that makes sense - to try and do a template file of this data is going to be very time consuming.......
Thanks for considering thisHi, 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.
