Forum Discussion
Creating new column concatenating data from two or more rows
Demo data:
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.
Please follow the steps shown in my file. I would not bother so much about the last comma becoming a &
10 Replies
What would be the expected outcome if Pam's last name is "Carter" ?
See my response to Ashish.
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!
Hi,
I cannot understand what the input table is and what the output table is?
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=19kbc9bq&dl=0
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.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.
