joining columns together (name)

[jcastr02]

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. 

[v-kelly-msft · Accepted 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.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

You have in text.combine M and concat in Dax

https://docs.microsoft.com/en-us/powerquery-m/text-combine

[jcastr02]

@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")