Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I had a first name and last name column which I joined to "Name of team member", and then did a split column since the team members middle initial was at beginning of last name ex. Amy west was showing as M.West, Amy. Is there a way I can join into a new column "name of team member.1" and "name of team member.2"? see pic above thank you.
Solved! Go to Solution.
Hi @jcastr02 ,
You need 2 measures as below:
Name of team member 1 =
VAR a =
ISERROR ( SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ) ) )
RETURN
IF (
a = TRUE (),
CONCATENATE (
SELECTEDVALUE ( 'Table'[Last Name] ) & ",",
SELECTEDVALUE ( 'Table'[First Name] )
),
LEFT ( SELECTEDVALUE ( 'Table'[Last Name] ), 1 )
)
Name of team member 2 =
VAR a =
ISERROR ( SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ) ) )
VAR b =
LEN ( SELECTEDVALUE ( 'Table'[Last Name] ) )
- SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ),, 0 )
VAR c =
RIGHT ( SELECTEDVALUE ( 'Table'[Last Name] ), b )
RETURN
IF (
a = FALSE (),
CONCATENATE ( c & ",", SELECTEDVALUE ( 'Table'[First Name] ) ),
BLANK ()
)
Finally you will see:
For the related .pbix file,pls click here.
Hi @jcastr02 ,
You need 2 measures as below:
Name of team member 1 =
VAR a =
ISERROR ( SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ) ) )
RETURN
IF (
a = TRUE (),
CONCATENATE (
SELECTEDVALUE ( 'Table'[Last Name] ) & ",",
SELECTEDVALUE ( 'Table'[First Name] )
),
LEFT ( SELECTEDVALUE ( 'Table'[Last Name] ), 1 )
)
Name of team member 2 =
VAR a =
ISERROR ( SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ) ) )
VAR b =
LEN ( SELECTEDVALUE ( 'Table'[Last Name] ) )
- SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ),, 0 )
VAR c =
RIGHT ( SELECTEDVALUE ( 'Table'[Last Name] ), b )
RETURN
IF (
a = FALSE (),
CONCATENATE ( c & ",", SELECTEDVALUE ( 'Table'[First Name] ) ),
BLANK ()
)
Finally you will see:
For the related .pbix file,pls click here.
You have in text.combine M and concat in Dax
https://docs.microsoft.com/en-us/powerquery-m/text-combine
@amitchandak How can I just get value that has the name into one column? If I concatenate, it will bring the values together, example west, amy should just show in new column as West, Amy (want to it disregard the "m")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |