Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Josh_BI_UK
Helper II
Helper II

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
1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
Josh_BI_UK
Helper II
Helper II

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

Duplicate post - can it be delted?

 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

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.

MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Seward12533
Solution Sage
Solution Sage

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])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.