Group By needing help

[Anon29]

Hi all, this has been doing my head in so hoping someone can help.

The data below is an example of one phone conversation and the order of the Agents talking is in order of the contact from each agents starting from 411 to the last contact person being 2111.

I am wanting to group the data so its one row per, Date, Name, TalkId, Talkorder, and stats column are just sum.

The Talkorder is the order the agents spoke in the TalkID. Cat Girl talked first so her order starts with 41x.. If Cat Girl finishes the call and passes the call to and another Agent, then Cat Girl's talkorder will stop. Some talk orders are 4 (or more) digits long such as Aqua Boy.

Cat Girls initial talk finished at Talkorder 417. If the call gets passed back to Cat Girl then she will have a new Talkorder starting with something like 2111. This is treated as two calls from Cat Girl in the same Talk ID, so I need this to be in two rows and there Stats next to Cat girls 41x and 211x TalkID.

If the starting two digits of the Talkorder and the Agent are the same, then they are the same call, eg Bat Man 811 and Bat Man 8112 can be groupped as one call, hence why the only stat for his Talkorder starting 81x..

This is what my raw data looks like for one TalkID. I need this to work on all TalkID's.

Date	Agent	Talk ID	Talkorder	Stat (seconds)
29/08/2023	Cat Girl	abc123	411	900
29/08/2023	Cat Girl	abc123	414
29/08/2023	Super Girl	abc123	611	245
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	650
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	360
29/08/2023	Bat Man	abc123	818
29/08/2023	Bat Man	abc123	8110
29/08/2023	Ken	abc123	1311	240
29/08/2023	Super Girl	abc123	6113
29/08/2023	Bat Man	abc123	8112
29/08/2023	Super Man	abc123	1611	400
29/08/2023	Barbie	abc123	1711
29/08/2023	Barbie	abc123	1712	300
29/08/2023	Barbie	abc123	1713
29/08/2023	Cat Girl	abc123	2111	360

This is what I want the result to look like in Power Query:

[edhans]

@Anon29 did this help?

[edhans · Accepted solution]

Try this code @Anon29 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZM9D4MgEIb/imE2kQNEHUuHDk2njsYBGweTph+mDv33Reyg9a6B5SDk4fLe3Xt1zUSV8TITXEiWsr19JYd+uLqrbS/g3xSAixXnrEmDcOVisoXP46MbNrj22YXKwz+IuPwSx3HtOgYucNg4+GRvS7b0VYayU4E6R/qN0kS7UZYob/ccbWLu7yUMQGkmaGIsBD0dUofWWMZ0muPwsVuBIL/OQzSQViW8hOv469MfHOY9UNiWGTu0fbeiC9pPCDsJkcGZI/ZFzB7xc2w+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Agent = _t, #"Talk ID" = _t, Talkorder = _t, #"Stat (seconds)" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BS"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Stat (seconds)", Int64.Type}}),
    #"Added TalkOrder Group" = Table.AddColumn(#"Changed Type", "TalkOrder Group", each Text.Start([Talkorder], 2), type text),
    #"Grouped Rows" = 
        Table.Group(
            #"Added TalkOrder Group", 
            {"Date", "Agent", "Talk ID", "TalkOrder Group"}, 
            {
                {"Stat (Seconds)", each List.Sum([#"Stat (seconds)"]), type nullable number}, 
                {
                    "TalkOrder ID", 
                    each 
                      let
                        varTalkOrderLen = Text.Length(_[Talkorder]{0}) -1
                      in
                      Text.Start(_[Talkorder]{0}, varTalkOrderLen)
                }
           }
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Date", "Agent", "Talk ID", "TalkOrder ID", "Stat (Seconds)"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"TalkOrder ID", type text}, {"Stat (Seconds)", Int64.Type}})
in
    #"Changed Type1"

It returns this:

What the code does:

1. I added a group ID that is just the first two characters to it groups by 41, 61, etc.
2. Within the group, I find the length of the first Talk Order ID for that Talkorder ID grouping and subtract 1.
3. I then truncate the talkorder ID by that length

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.