Forum Discussion
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 use the grouped rows to generate 3 columns one called "Hunt Group Name" so I know the type of call (i.e. sales, enquiries and so on), the "staff member" who answers and then "duration" of the call. I am trying to populate these new columns with a value that is conditional to another column called "type". So for example, I only wish to return the staff member if this "type" column equals "INC" otherwise leave it null. Likewise i only want to return the "call routing" value if the "type" column equals "I/T" or "I/U" else leave it null.......
Here is the current code,
#"Grouped Rows" = Table.Group(#"Replaced Value1",
{"number", "time", "date"},
{
{"Call Routing", each _, type table [id=number, department=nullable text, extension=nullable text, user=nullable text, date=nullable date, time=nullable time, duration=nullable time, duration_secs=nullable number, number=nullable text, area=nullable text, ddi=nullable text, ring_time=nullable time, ring_time_secs=nullable number, type=nullable text]},
{"Hunt Group Name", each [user]{List.PositionOfAny([user],{"Hunt Group buy", "Hunt Group sell", "Hunt Group customer_service_enquiry"})}},
{"Staff Member", each [user]{List.PositionOfAny([user],{"Me", "You", "Someone Else"})}},
{"Duration", each List.Max([duration])}
}
),
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.
11 Replies
Hi,
My original post which I REALLY need an answer to is marked as spam - https://community.powerbi.com/t5/Power-Query/Group-by-and-conditional-column/m-p/2233698#M66235
No answer after 24 hours from mods........any suggestions?Thanks,
Hi All,
I am facing an issue post extracting Data from Data bricks into powerbi.
I checked in data lake the data was upto date. however when post extracting Data into powerbi from data bricks it's showing less number of data even after refreshing N number of tiems.
Eg: data contains 48 weeks of data. Post extraction into powerbi it's showing only first 26 weeks of data. Anyone know what's the issue.please help tat wuld be grt thanks in advance ✌️
Hello @danish169,
My apologies for any inconvenience you may have been through trying to get your question posted. Your post has been marked as not spam and it has been moved back. The platform scans for spam automatically and we monitor all flagged posts to determine whether or not the content should be unmarked. There are multiple variables the platform scans for and I have no way of knowing exactly what triggered your post to be flagged. Some common trigger items are copy and pasting content in and multiple edits. You should be good to go on this post. Thanks for being a part of the community!
Regards,Kinjal
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.
Good morning,
I am so grateful for that response : D you are an absolute legend. It works perfectly
ThanksI dont think I ever thanked you for the working version. I was able to implement it in full so appreciate the insights 😄
