Creating new column concatenating data from two or more rows

[newhopepdx]

Demo data: 

https://www.dropbox.com/scl/fi/lqmtrqarld385r3n0ujo0/Demo.pbix?rlkey=wvvh6su95upyzybxvdu71vywj&st=kw...

https://www.dropbox.com/scl/fi/91p4ogkga9lwy24ck4fan/Demo.xlsx?rlkey=4fqec93ab2r0mh429xwfvig09&st=k3...

I've inherited a Family table which has pulled each family member's first name, concatenated them adding the last name. I need to add a "lastname" column for sorting purposes. In the case where there are multiple lastnames, I'm happy with using the last name from the first individual for the sort column.

[v-kpoloju-msft]

Hi @newhopepdx,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @Ashish_Mathur, and @lbendlin, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @newhopepdx,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[newhopepdx]

When there are two (or more) different lastnames for members of the family, I'm good with using the lastname of the first person listed, so Alex Jones, Pam Carter & Samatha Jones would have Jones in the Family Sort column. 

Just wanting to get a closer sort than having only a first name sort, like now.

Thanks!

[Ashish_Mathur]

Hi,

I cannot understand what the input table is and what the output table is?

[newhopepdx]

The desired output is the Family Table with an additional column: Lastname.

Two possibilities: 1) For only one lastname, e.g., for John & Susan Brown it would be Brown OR 2) if there are multiple lastnames, then the first person's last name, e.g.,  Beth Williams & Andrew Peters it would be Williams.

Ibendlin asked, "What if Pam's name was Carter"... Then #2 above would apply.

I realize this isn't a perfect solution, but I just needing a better (closer) sort than having to rely on a matrix sorted by first name.

The "People" table is a secondary table available that could be used to create the solution (??). It just contains every individual with a linking field to their "family".

The PBIX file is available here: https://www.dropbox.com/scl/fi/lqmtrqarld385r3n0ujo0/Demo.pbix?rlkey=wvvh6su95upyzybxvdu71vywj&st=19...

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[newhopepdx]

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.

[Ashish_Mathur · Accepted solution]

Please follow the steps shown in my file.  I would not bother so much about the last  comma becoming a &

[lbendlin]

What would be the expected outcome if Pam's last name is "Carter"  ?

[newhopepdx]

See my response to Ashish.