Resolver I

1 year ago

Solved

Table.Group problem

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

Need Help

SamsonTruong

1 year ago

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

7 Replies

SamsonTruong

Super User

1 year ago

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

Resolver I

1 year ago

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

Super User

1 year ago

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

Ashish_Excel

Solution Supplier

1 year ago

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
Resolver I
1 year ago
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.

Forum Discussion

Table.Group problem

7 Replies

Related content

Dynamic Table.group

Logic problem?

Slider problems

Problems in UK South

Refresh problem