Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello, I'm trying to split "Name" into "First Name" and "Last Name". How do I do this?
I found the DAX function "left", but you have to provide a character count to split on, rather than a character to split ON, aka space in this case. I also tried :
First Name = LEFT( 'Table1'[Name], SEARCH(" ", 'Table1'[Name], 0, LEN('Table1'[Name]) ) )
Thank you in advance for your help!
Solved! Go to Solution.
Hi @SarWal,
In your scenario, as you want to split a column based on space rather than a character, you need to replace the space with a character use SUBSTITUTE() function, then split the value use Search() function. Please refer to screenshots below:
First name = LEFT(SUBSTITUTE(Table1[Name]," ","-"),SEARCH("-",SUBSTITUTE(Table1[Name]," ","-"))-1)
Last name = RIGHT(SUBSTITUTE(Table1[Name]," ","-"),LEN(SUBSTITUTE(Table1[Name]," ","-"))-SEARCH("-",SUBSTITUTE(Table1[Name]," ","-")))
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
I am a DAX beginner, so this is probably an inefficient way to do this.
Method assuming you have a delimiter like ,. Assume the schema is LNAME, FNAME (change col names below to suit)
1. Find the position of comma, then subtract one. That is length of LNAME.
2. Trim from left start position 1 the num above.
=TRIM(
//trim from left, the number of chars in FIND
LEFT(Table,
//this find finds the comma position from the start char on left, then subtracts one for last name length
FIND(",",Table[NameCol],1)-1))
3. Find Len of whole name (e.g. LNAME, FNAME), subtract 1 for the comma (I learnt spaces are not counted doing this exercise).
4. Subtract Len whole name-1 from len last name to get FNAME len
5. Trim Right on that len
=TRIM(
//trim from right the name col,
RIGHT(Table[NameCol],
//the length of whole name
LEN(Table[NameCol])
//minus the length of the last name
-(FIND(",",Table[NameCol],1))-1)
//to get the first name
)
I built this logic up over a few columns to determine length of strings, comma position etc.
FirstName = PATHITEM(SUBSTITUTE('fTable'[FullName];" ";"|");1)
Lastname = PATHITEMREVERSE(SUBSTITUTE('fTable'[FullName];" ";"|");1)
=PATHITEMREVERSE(SUBSTITUTE(Performance[Performance Review Rating],"-","|"),1)
thank you so much guru
Hi @SarWal,
In your scenario, as you want to split a column based on space rather than a character, you need to replace the space with a character use SUBSTITUTE() function, then split the value use Search() function. Please refer to screenshots below:
First name = LEFT(SUBSTITUTE(Table1[Name]," ","-"),SEARCH("-",SUBSTITUTE(Table1[Name]," ","-"))-1)
Last name = RIGHT(SUBSTITUTE(Table1[Name]," ","-"),LEN(SUBSTITUTE(Table1[Name]," ","-"))-SEARCH("-",SUBSTITUTE(Table1[Name]," ","-")))
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
Hi, good afternoon. I know this is really old, but DAX doesn't work for me if I use negative values like -1 but it does if I apply +1, also with the Right formula since I', using"-" for the search formula. Do you know why is this happening?
Hi,
Your solution is great and helpful to me. Do you have any idea if there are first mid last name of someone and you only want to get the last name?
For example, if there is a text called "John George Washington Bosh Wang" and you only want to get the Wang out, do you have any idea?
An elegant solution for navigating delimiters in Vertipaq is to leverage the PATHITEM() and PATHLENGTH() functions using SUBSTITUTE().
For example, if your delimiter was "." and you wanted to return "Simon" (the 6th element) from "Hello.Friend.My.Name.Is.Simon.Nuss":
PATHITEM( SUBSTITUTE( [Column1], ".", "|" ), 6 )
If you want to return the last occurance, i.e. "Nuss", you can perform:
Result = VAR Nodes = SUBSTITUTE( [Column1], ".", "|" ) RETURN PATHITEM( Nodes, PATHLENGTH( Nodes ) )
If you want to return the 3rd last occurance, i.e. "Is", you can perform:
Result = VAR Nodes = SUBSTITUTE( [Column1], ".", "|" ) RETURN PATHITEM( Nodes, PATHLENGTH( Nodes ) - 3 )
Good luck!
Simon
Hi @v-qiuyu-msft,
I am trying to accomplish the same thing as mentioned in the initial post; however, I am having issues with the formula you provided around the SEARCH function. It's rendered an error message stating, "The search Text provided to function 'SEARCH' could not be found in the given text". Here is an example:
The Item Description is "COMPANY PRODUCT NAME" and I need to split this up into two separate columns that read column #1 "COMPANY" and column # 2 "PRODUCT NAME". The formula I'm using is:
Module Type = RIGHT(SUBSTITUTE(WFR_New_Module_View[Item Description],"","-"),LEN(SUBSTITUTE(WFR_New_Module_View[Item Description],"","-"))-SEARCH("-",SUBSTITUTE(WFR_New_Module_View[Item Description],"","-")))
Any idea why this isn't working?
The COMPANY PRODUCT NAME column have a "-" ?. The error that indicates is that cant' found this - in your column
I had to do an if/else for some reason.
IF(the value contains ":", mid(the value, ":"), "")
Otherwise it puked as you described.
@Vvelarde Got it, that makes sense. So say that, using the same example, the Item Description is "COMPANY NAME PRODUCT NAME" and I need to split this up into two separate columns that read column #1 "COMPANY NAME" and column # 2 "PRODUCT NAME". Right now the formula is working using the fix you suggested, but it is splitting it up so that coulmn #1 reads "COMPANY" and column # 2 reads "NAME PRODUCT NAME". Hopefully that makes sense.
You need to start to search after find the first space:
SEARCH(" ",column,SEARCH(" ";column)+1)
Here is the current string I'm using:
Module Type = RIGHT(SUBSTITUTE(WFR_New_Module_View[Item Description],""," "),LEN(SUBSTITUTE(WFR_New_Module_View[Item Description],""," ")) - SEARCH(" ",SUBSTITUTE(WFR_New_Module_View[Item Description],""," ")))
Using this string, it will take the text "WORKFORCE READY TIME KEEPING" and transform it into column #1 "WORKFORCE" and column #2 "READY TIME KEEPING".
Given your suggestion, where would the revised string SEARCH(" ",column,SEARCH(" ";column)+1) fit into the above string?
I have tried: Module Type = RIGHT(SUBSTITUTE(WFR_New_Module_View[Item Description],""," "),LEN(SUBSTITUTE(WFR_New_Module_View[Item Description],""," ")) - SEARCH(" ",column,SEARCH(" ";column)+1)))
...but I receive an error message. Thoughts?
hi @amtanner
Replace
SEARCH(" ",column,SEARCH(" ";column)+1
to
SEARCH(" ",WFR_New_Module_View[Item Description],SEARCH(" ";WFR_New_Module_View[Item Description])+1
Have you considered doing this in Query Editor? You can simple select the column, select split column from the ribbon and choose split by delimiter. Then select the space.
It will create two new columns.
Proud to be a Super User!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.