Reply
Frequent Visitor
Posts: 13
Registered: ‎07-18-2018
Accepted Solution

Extract text left of "@" else use whole text - PowerQuery

Hi,

 

Using Power BI / PoewerQuery, I would like to extract or clean up a column which has fields with either emails address or names, all in the same column. e.g. Clark.Kent@DailyPlanet.com, Clark.Kent, Wally West, Lex.Luthor@LexCorp.com, Jimmy Olsen.

 

I have two columns. 1st column "UserNames_Raw", 2nd column "UserName_Clean.

 

If field within"UserNames_Raw"  contains the "@" then I would like to exact the text before the "@" ; if it doesn't then I would like to use the whole text string. The output will go into my 2nd column in both cases.

 

e.g.

UserName_RawUserName_Clean
Clark.Kent@DailyPlanet.comClark.Kent
Wally WestWally West
Lex.Luthor@LexCorp.comLex.Luthor
Jimmy OlsenJimmy Olsen

Accepted Solutions
Super User
Posts: 2,139
Registered: ‎09-19-2016

Re: Extract text left of "@" else use whole text - PowerQuery

Hi @Josh_BI_UK,

 

Agree with the solution of @Seward12533 however you can also use one of the solutions below:

 

DAX:

UserName_Clean_DAX = 
VAR Search_value =
    SEARCH ( "@"; Names[UserName_Raw]; 1; 1 )
RETURN
    LEFT (
        Names[UserName_Raw];
        IF ( Search_value = 1; LEN ( Names[UserName_Raw] ); Search_value - 1 )
    )

 

M Language:

Text.Start([UserName_Raw],
if
Text.PositionOf ([UserName_Raw], "@") = -1 then

Text.Length ([UserName_Raw])

else 
Text.PositionOf ([UserName_Raw], "@")

)

Just giving alternatives, lots of different ways to do the same thing.

 

Regards,

MFelix



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

Proud to be a Datanaut!




View solution in original post


All Replies
Frequent Visitor
Posts: 13
Registered: ‎07-18-2018

If text contains "@" sign extract text right of sign else use text

Hi,

 

I'm trying to clean up a column which has mixed email addresses and user names within it ("UserName_Raw"). The end result being a column i.e. "UserName_Clean" with just names in it.

 

If the field contains an email address I want to extract all the text to the left of the "@" and use that as the data for "UserName_Clean", if the original field i.e. "[UserName_Raw]" doesn't contain an "@" then I would just like to use the text within it as the default data. 

 

To clarify:

 

UserName_Raw UserName_Clean

Clark.Kent@DailyPlanet.comClark.Kent
Wally WestWally West
Lex.Luthor@LexCorp.comLex.Luthor
Jimmy OlsenJimmy Olsen
New Contributor
Posts: 444
Registered: ‎02-15-2018

Re: Extract text left of "@" else use whole text - PowerQuery

Easiest way is to Use Split Column feature of PowerQuery found on the modeling TAB and choose the @ sign as your delimeter.

 

Alternatively you can use DAX to create column

USERNAME_CLEAN = VAR POS = FIND("@",[USERNAME_RAW],-1) RETURN IF(POS>0,LEFT(USERSNAME_RAW],POS,[USERNAME_RAW])
Super User
Posts: 2,139
Registered: ‎09-19-2016

Re: Extract text left of "@" else use whole text - PowerQuery

Hi @Josh_BI_UK,

 

Agree with the solution of @Seward12533 however you can also use one of the solutions below:

 

DAX:

UserName_Clean_DAX = 
VAR Search_value =
    SEARCH ( "@"; Names[UserName_Raw]; 1; 1 )
RETURN
    LEFT (
        Names[UserName_Raw];
        IF ( Search_value = 1; LEN ( Names[UserName_Raw] ); Search_value - 1 )
    )

 

M Language:

Text.Start([UserName_Raw],
if
Text.PositionOf ([UserName_Raw], "@") = -1 then

Text.Length ([UserName_Raw])

else 
Text.PositionOf ([UserName_Raw], "@")

)

Just giving alternatives, lots of different ways to do the same thing.

 

Regards,

MFelix



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

Proud to be a Datanaut!




New Contributor
Posts: 444
Registered: ‎02-15-2018

Re: If text contains "@" sign extract text right of sign else use text

Duplicate post - can it be delted?

Frequent Visitor
Posts: 13
Registered: ‎07-18-2018

Re: If text contains "@" sign extract text right of sign else use text

 Hi @Seward12533 , did you mean the whole post be deleted or just the solution from @MFelix?

 

MFelix solution was just what needed, however if the same problem has been solved else where and it is the rules of the forum to delete repeat questions / solutions, then fair enough.

 

However may be other similar users might phrase their problem the same way or similar to the way I did, so please consider leaving it.

 

Either way, cheers @MFelix

Highlighted
New Contributor
Posts: 444
Registered: ‎02-15-2018

Re: If text contains "@" sign extract text right of sign else use text

Must have posted this on the wrong one. When you originally posted the question was posted twice.  And one had an answser and the other didint. Certinaly don't want to delete a thread with a solution.