Forum Discussion
split column based on different delimiters
Hi,
I have a column in a table imported from a SharePoint list looking like this:
| ProjectLead |
| LastName1, FirstName1, LastName2, FirstName2 |
| LastName1, FirstName 1 |
| LastName3 FirstName3 |
| LastName2, FirstName2, LastName3 FirstName3 |
So there are different inputs of the names, sometimes last name and first name are divided by a comma, sometimes just by a blank space. As you can see above, there is also a combination where the one name is divided by comma and the second name is not, but both names are dived from each other by comma.
Ideally I would like to have an output in PowerBi where I have:
FirstName1 LastName1, FirstName2 LastName2
no matter the combination of names.
I was thinking of first splitting the column into first names and last names and then concatenate again in the right order. My issues are now the different delimiters I need to split the cell and also how to concatenate a cell that has two names.
Anyone can help? Thank you in advance!
3 Replies
Anonymous , Not very clear. But you can split the column
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Logically Anonymous how would you propose doing this?
You cannot use commas as the delimiter because that sometimes splits the names, not just groups of names.
You cannot use spaces as the delimiter because someone could have a space in their name - Jones, Mary Ann for example.You cannot count spaces or commas because the data is inconsistent.
If you can come up with logic that will work, then we can help. Text.BeforeDelimiters(), Text.AfterDelimiters(), and Text.BetweenDelimiters() will likely be the most useful here, but without having some sort of logical consistency to work with, it is going to be difficult to make this work.
Hi edhans ,
Thank you for looking into my problem.
You are right, it is exactly my issue that there is no logical consitency in the data in order to write a general rule. .I was hoping I had overseen something that you experienced users could see 🙂.
But I think it is not doable then.
Thanks again!
Lone
