Creating new column concatenating data from two or more rows

Ashish,

Getting closer! Your Table.Group got me on the right track.

Here's what I've done (and where I'm still stuck)...

1) I duplicated the Family table and Removed Duplicates on the FamilyId column. This got me a list of family lastnames.

2) I did a Full Outer Join between People and UniqueFam tables, expanded the  results keeping only the UniqueFam.Lastname column. So now (in the People table) I've got the Family Name I want to use with each person.

3) Renamed the last column to FamilyName

4) Grouped By FamilyName & FamilyId

5) Here's where I'm stuck...

This is the contents of the row 3 - Jones table

I'm trying to create an AddColumn which will result in:

"Alex, Pam Carter, Samantha"

i.e., Text.Combine resulting in "Firstname" IF the Lastname = FamilyName ELSE  "Firstname + " " + Lastname

Here's what I've tried but the "else" portion is what I can't figure out.

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine( if [Count][Lastname] = [Count][FamilyName] then [Count][Firstname] else {[Count][Firstname], " ", [Count][Lastname]}, ", "))
in #"Addded Custom"

Once I get the proper code for this last step, then

6) I can add the FamilyName to the end of the [Custom] column

So, "Alex, Pam Carter, Samantha" would become "Alex, Pam Carter, Samantha Jones"

7) If there's more than one  ", " then replace the last one it with " & "

"Alex, Pam Carter & Samantha Jones"

😎 Now be able to sort the column in Step 7 via the FamilyName.