Grouping Problem in Power Query

[Anon29]

Hi all, I am struggling to figure how to get this grouping to work in the Power BI desktop Power Query. 

My goal is to group the data so that it shows in one row the Date, Agent Talk ID, Talkorder and stat1 and stat2 all in one row. It needs to be done in Power Query.

Here's my rawdata. The full data has many different Talk ID with the similar structure.

Date	Agent	Talk ID	Talkorder	Stat1	Stat2
12/06/2023	Bat Woman	zqy321	411	300
12/06/2023	Bat Woman	zqy321	414		1
12/06/2023	Wonder Girl	zqy321	611	500
12/06/2023	Wonder Girl	zqy321	612
12/06/2023	Wonder Girl	zqy321	613		1
12/06/2023	Bat Woman	zqy321	416
12/06/2023	Bat Woman	zqy321	417
12/06/2023	Robinhood	zqy321	811
12/06/2023	Robinhood	zqy321	812	670
12/06/2023	Hook	zqy321	1111		1
12/06/2023	Hook	zqy321	1112
12/06/2023	Hook	zqy321	1113	360
12/06/2023	Robinhood	zqy321	818
12/06/2023	Robinhood	zqy321	8110
12/06/2023	Ken	zqy321	1311	480
12/06/2023	Wonder Girl	zqy321	6113
12/06/2023	Robinhood	zqy321	8112		1
12/06/2023	Super Man	zqy321	1611	100
12/06/2023	Barbie	zqy321	1711
12/06/2023	Barbie	zqy321	1712	423
12/06/2023	Barbie	zqy321	1713		1
12/06/2023	Bat Woman	zqy321	2111	901	1

I would like the result to be like this below.  My problem is getting the Talkorder to display like below. If the talkorder for the same agent starts with the same digits, then it can be grouped together, like Wonder Girl having 611 and 6113 can be together since they start with 61. 

Bat Woman has Talkorders that start with 41X and 211X which means she talked to the customer twice on the same call (call transferred back to Bat Woman). 

Can someone please help me on this

[BA_Pete · Accepted solution]

Hi @Anon29 ,

The issue here is that the logic required appears to change between [Talkorder] values.

Per your example, you seem to want to group Bat Woman on a 41X and 211X basis. This would be solved by creating a new [Talkorder] column to group on that just cuts off the last digit, something like this:

Text.Start(Text.From([Talkorder]), Text.Length(Text.From([Talkorder])) - 1)

However, applying this logic then fails to group Robin Hood under a single [Talkorder] value of 81, as this would require evaluating [Talkorder] on both a 81X and 81XX basis. Similarly, it appears as though Wonder Girl's calls need to be evaluated on both a 61X and 61XX basis. This is mixed logic. 

Can you provide any additional information that would identifiy the type of logic that should be used in each scenario based on the data please?

Pete

[Anon29]

Thanks Pete, I think I may have found a working solution, just need to test it on a larger set of data. What I ended up doing was adding an extra column to capture the first two numbers of the Talkorders. Then grouping everything by date, name, talkid, newtalkorder. I then added the Table.Min column record based of the subtable of the grouped data to find the first Talkorder. Not sure if this is the most efficient way though.

[BA_Pete]

Ok. This method doesn't sound like it would give you the output you requested intially. Taking the record with the min 2-digit [newtalkorder] would remove [newtalkorder] = 41 for Bat Woman, wouldn't it?

Pete

[Anon29]

No because the 2 digits ended up grouping all the talk orders the way I wanted it to and also seperate Bat Woman's 41x and 211x talkorders. Then by adding in the min column of the rawdata Talkorder into the sub table of the groupping, I was able to get what I wanted. 

I also bookmarked your links as well, they are extremely useful and will definetitly be referring back to it when I'm working with sub tables within columns.