Forum Discussion
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 Gr...
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
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
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.
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 FinalSamson,
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!
Awesome, glad to hear it worked!