Group by and conditional column

[danish169]

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])}
}
),

[danish169]

I dont think I ever thanked you for the working version. I was able to implement it in full so appreciate the insights 😄 

[danish169]

Good morning,

I am so grateful for that response : D you are an absolute legend. It works perfectly

Thanks

[danish169]

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,

[Kinjal]

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

[Anonymous]

Can I get a reply soon plzzz from anyone. ??? I know it's month of joy as Christmas 😁 But I need some help from you people 

[Anonymous]

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 ✌️

[v-angzheng-msft]

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.

[danish169]

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 this

[v-angzheng-msft · Accepted solution]

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.

[danish169]

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.

[v-angzheng-msft]

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 Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.