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
rajeshchawla
Helper I
Helper I

How to remove all characters except alpha numeric in text column

The column is populated with data that contains quite a few Unicode characters. The rows of interest to me are the ones where the characters are only in the range of a-z (upper or lower case) or 0-9. To me, the replace functionality was not enough as there are too many characters to replace.

 

I did find a potential solution using regular expressions & R in the post: http://radacad.com/power-bi-and-regular-expressions, however, I was hoping for an alternative that did not use R.

 

Pointers / Suggestions appreciated.

 

Regards,

Rajesh

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

@rajeshchawla

Here is one way of doing it. The below code filters rows to only those where Text is equal to just the alphanumeric characters from Text.

This assumes the last step of the query is PreviousStep and the column of interest is named Text.

 

= Table.SelectRows( PreviousStep, each [Text] = Text.Select([Text],{"a".."z","A".."Z","0".."9"} ) )

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

@rajeshchawla

Here is one way of doing it. The below code filters rows to only those where Text is equal to just the alphanumeric characters from Text.

This assumes the last step of the query is PreviousStep and the column of interest is named Text.

 

= Table.SelectRows( PreviousStep, each [Text] = Text.Select([Text],{"a".."z","A".."Z","0".."9"} ) )

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you!

Anonymous
Not applicable

Could you provide more information regarding your requirement? Do you want identify if the column contains any invalid character, such as !@#?

Added more detail to original question.

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.