Group By - updated problem

[Anon29]

Hi all, hoping someone can help.

The data below is an example of one phone conversation and the order of the people talking is in order from first contact person being 411 to the last contact person being 8113.

I am wanting to group the Talkorder with the Name. I have tried to split the Talkorder by first two digits and use group by that way, but that doesn't work because all the Talkorders for Bat Man can start with a Talkorder of 811 and continue on till the talkorder is 8113 (sometimes even 6 digits long). Also I want to maintain the Talkorder for the names that genuinely start with three digits, like Aqua Boy, Ken, Super Man and Barbie. 

This is what my raw data looks like.

Date	Name	Talk ID	Talkorder
29/08/2023	Cat Girl	abc123	411
29/08/2023	Cat Girl	abc123	414
29/08/2023	Super Girl	abc123	611
29/08/2023	Super Girl	abc123	612
29/08/2023	Super Girl	abc123	613
29/08/2023	Cat Girl	abc123	416
29/08/2023	Cat Girl	abc123	417
29/08/2023	Bat Man	abc123	811
29/08/2023	Bat Man	abc123	812
29/08/2023	Bat Man	abc123	814
29/08/2023	Bat Man	abc123	816
29/08/2023	Aqua Boy	abc123	1111
29/08/2023	Aqua Boy	abc123	1112
29/08/2023	Aqua Boy	abc123	1113
29/08/2023	Bat Man	abc123	818
29/08/2023	Bat Man	abc123	8110
29/08/2023	Ken	abc123	1311
29/08/2023	Bat Man	abc123	8112
29/08/2023	Super Man	abc123	1611
29/08/2023	Barbie	abc123	1711
29/08/2023	Barbie	abc123	1712
29/08/2023	Barbie	abc123	1713
29/08/2023	Bat Man	abc123	8113

This is what I want the end result to look like.  I want to maintain the Talkorder so I can sort from lowest number to highest number to get the initial talk order of each person, for each Talk ID.

If it's not possible to have the Talkeorder in that result, I am happy with a ranking list from smallest number to largest number. I just want a way to sort each Talk ID by the Talkorder, but please keep in mind that the raw data is not sorted nicely like in the example and it will be a mix of many different Talk ID not in order. I do not want to use the sort function in Power Query as it makes the refreshes too long.

[mlsx4 · Accepted solution]

Hi @Anon29 

I think you can reach this behaviour in Power Query using Group by like in the screenshot below:

[Anon29]

Thanks everyone, looks like I gave the wrong data, but the solutions you have given me is correct for the data that I have.

[ChielFaber]

You can create a rownumber per TalkID with the group by function best described in the following blog post by Radacad:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

This creates a row number per talkID. The last thing you would need to do is filter on rownumber 1 (or index = 1) using the UI.

Greetings, 

Chiel

[AlienSx · Accepted solution]

Hi, @Anon29 looks like you need to select min Talkorder withing each Talk ID and Name.

let
    Source = raw_data_table,
    group = Table.Group(Source, {"Name", "Talk ID"}, {{"all", each Table.Min(_, "Talkorder")}}),
    expand = Table.ExpandRecordColumn(group, "all", {"Date", "Talkorder"})
in
    expand

[mlsx4 · Accepted solution]

Hi @Anon29 

I think you can reach this behaviour in Power Query using Group by like in the screenshot below: