Table.Group problem

[newhopepdx]

Given this test data:

I need to create a table (like the one shown below) that combines the names:
  1. The GrpLeader field contains the PersonId of the group leader

  2. The resulting GroupName should start with the group leader's firstname, followed by the other group member's firstname, and end with the lastname. Sometimes the Group Leader is not the person with the lowest PersonId as in group 12

  3. It needs to account for the possibility of more than two members in the group as in group 13

  4. It needs to handle different lastnames as in group 12

The output should look like this:

Table.Group seems to be the starting point, but I'm stuck on the details of adding the last name and making sure that the other details noted are followed. Thanks for any suggestions.

Steve

[Ashish_Excel]

Hi,

Just in case you want a calculated column and measure solution, then try this approach

Write these 3 calculated column formulas

Calculated Column 1 = =LOOKUPVALUE(Data[FirstName],Data[Personld],[GrpLeader])&if(CALCULATE(DISTINCTCOUNT(Data[LastName]),FILTER(data,Data[Grpld]=EARLIER(Data[Grpld])))>1," "&LOOKUPVALUE(Data[LastName],Data[Personld],[GrpLeader]),if(CALCULATE(COUNTROWS(Data),FILTER(data,Data[Grpld]=EARLIER(Data[Grpld])))=1," "&Data[LastName],BLANK()))

Calculated column 2 = =if(or(CALCULATE(COUNTROWS(Data),FILTER(data,Data[Grpld]=EARLIER(Data[Grpld])))=1,CALCULATE(DISTINCTCOUNT(Data[LastName]),FILTER(data,Data[Grpld]=EARLIER(Data[Grpld])))>1),Data[Calculated Column 1],CONCATENATEX(FILTER(Data,Data[Grpld]=EARLIER(Data[Grpld])),Data[FirstName],", "))

Calculated column 3 = =Data[Calculated Column 2]&if(CALCULATE(DISTINCTCOUNT(Data[LastName]),FILTER(data,Data[Grpld]=EARLIER(Data[Grpld])))>1," & "," ")&if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Grpld]=EARLIER(Data[Grpld])))>1,if(CALCULATE(DISTINCTCOUNT(Data[LastName]),FILTER(data,Data[Grpld]=EARLIER(Data[Grpld])))=1,CALCULATE(MAX(Data[LastName]),FILTER(Data,Data[Grpld]=EARLIER(Data[Grpld]))),CONCATENATEX(FILTER(Data,Data[Grpld]=EARLIER(Data[Grpld])&&Data[Personld]<>EARLIER(Data[GrpLeader])),Data[FirstName]&" "&Data[LastName],", ")),BLANK())

Now create a table visual and drag GrpID to the row labels.  Write this measure

Mesure = max(Data[Calculatd Column 3])

Hope this helps.

[newhopepdx]

Asnish,

This is an interesting concept, unfortunately I need to end up with a table that has no duplicate GrpId #'s so I can merge it with the People table, adding the names of the GroupName to each record. I don't see a way to do that outside of query editor.

Samson's suggestion works except for an error in the code (that I noted in my reply) and don't have enough M code experience to debug.

[SamsonTruong]

Hi @newhopepdx,

Please try the following Power Query M Code to create your desired table. Please replace 'YourTable' in the source step with your actual source table:

let
    Source = YourTable,
    AddFullName = Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName]),
    
    Grouped = Table.Group(
        AddFullName,
        {"GrpId"},
        {
            {"GroupData", each _, type table [GrpId=number, PersonId=number, GrpLeader=number, FirstName=text, LastName=text, FullName=text]}
        }
    ),
    
    AddGroupName = Table.AddColumn(Grouped, "GroupName", each
        let
            groupTable = [GroupData],
            leaderId = Record.Field(Table.First(groupTable), "GrpLeader"),
            leaderRow = Table.SelectRows(groupTable, each [PersonId] = leaderId){0},
            leaderFirst = leaderRow[FirstName],
            leaderLast = leaderRow[LastName],
            otherMembers = Table.SelectRows(groupTable, each [PersonId] <> leaderId),
            otherFirsts = Table.ToList(Table.TransformColumns(otherMembers, {"FirstName", each _ & ""})),
            lastNames = List.Distinct(Table.Column(groupTable, "LastName")),
            groupName = 
                if List.Count(lastNames) = 1 then
                    Text.Combine({leaderFirst} & otherFirsts, " ") & " " & lastNames{0}
                else
                    Text.Combine(List.Combine({{leaderRow[FullName]}, Table.Column(otherMembers, "FullName")}), " & ")
        in
            groupName
    ),
    
    Final = Table.SelectColumns(AddGroupName, {"GrpId", "GroupName"})
in
    Final

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

[newhopepdx]

Samson,

Thanks for the quick reply! It is almost there. The first condition in the "if List.Count" section is throwing an error for groups 11 & 13.

groupName = 
                if List.Count(lastNames) = 1 then
                    Text.Combine({leaderFirst} & otherFirsts, " ") & " " & lastNames{0}

Here's the error for the second row.  

[SamsonTruong · Accepted solution]

Hi @newhopepdx, apologies for the delay. Please try the revised solution below:

let
    Source = YourTable,
    AddFullName = Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName]),

    Grouped = Table.Group(
        AddFullName,
        {"GrpId"},
        {
            {"GroupData", each _, type table [GrpId=number, PersonId=number, GrpLeader=number, FirstName=text, LastName=text, FullName=text]}
        }
    ),

    AddGroupName = Table.AddColumn(Grouped, "GroupName", each
        let
            groupTable = [GroupData],
            leaderId = Record.Field(Table.First(groupTable), "GrpLeader"),
            leaderRow = Table.SelectRows(groupTable, each [PersonId] = leaderId){0},
            leaderFirst = Text.From(leaderRow[FirstName]),
            leaderLast = Text.From(leaderRow[LastName]),
            otherMembers = Table.SelectRows(groupTable, each [PersonId] <> leaderId),
            otherFirsts = List.Transform(Table.Column(otherMembers, "FirstName"), Text.From),
            lastNames = List.Distinct(List.Transform(Table.Column(groupTable, "LastName"), Text.From)),

            groupName =
                if List.Count(lastNames) = 1 then
                    Text.Combine({leaderFirst} & otherFirsts, " ") & " " & lastNames{0}
                else
                    Text.Combine(
                        List.Transform(
                            List.Combine({{leaderRow[FullName]}, Table.Column(otherMembers, "FullName")}),
                            Text.From
                        ),
                        " & "
                    )
        in
            groupName
    ),

    Final = Table.SelectColumns(AddGroupName, {"GrpId", "GroupName"})
in
    Final

[newhopepdx]

Samson,

Yes, that works. With your code as a BIG help, I explored what each line did and replaces the otherFirsts line you first supplied with this: otherFirsts = Table.ToColumns(otherMembers){3},  and it worked as well.

Thanks!

[SamsonTruong]

Awesome, glad to hear it worked!