How to Remove Special Characters in Excel? Using Power Query Text.Select

Power Query Training / M Query: Basics
-How to remove special characters in Excel?
-How to use Text.Select?

Power Query and Power BI uses the same type of data transformation techniques. Clear out special characters from your data entry by defining which characters to keep. Remove special characters without the need of Substitute formulas, no loops in VBA visual basic, simple and easy.

Data transformation and data cleansing using Power Query.

Functions Used:
-Text.Select([ColumnName], {"A".."Z", "a".."z", "0".."9"})
This keeps alphanumeric characters, both uppercase and lowercase letters and numbers

-Text.Select([ColumnName], {"0".."9"})
This keeps numbers only

Beginner Microsoft Excel Tutorial

#powerquery #powerquerytutorials #exceltutorial

[jeffn]

Brilliant, I had looked at another video which didn't work for me; also tried to get an AI to do it without success. Your one worked for me, thank you :-)

[JoseAntonioMorato]

I really liked the practical solution you developed in Power Query, but since I like to complicate things, I developed a solution with dynamic formulas that gives a similar result:
=BYROW(Table1[Data set], LAMBDA(a, TEXTJOIN(" ", , TEXTSPLIT(a, VSTACK(CHAR(SEQUENCE(47)), CHAR(SEQUENCE(7, , 58)), CHAR(SEQUENCE(6, , 91)), CHAR(SEQUENCE(133, , 123))), " ")))) 🤗

[cfsvczv]

Thanks, great video, how do I get rid of non-breaking spaces?

[jeffn]

Thank you, this is helpful but I ran into a problem. If I am trying to remove a control character such as this - & - I can do this by retaining the alpha characters only. However there are also genuine numbers between 0-9 in the dataset which I need. If I opt to retain the numbers 0-9 I get the control characters back again. Am I missing something?

[saloniarya]

Hello,
I have encountered an error:
"Expression error: The name 'Text.Select' wasn't recognized. Make sure it's spelled correctly."
Please help me resolve this error. Your help would be greatly appreciated.
Thank you

[MrThapaliya]

Great Video, thanks. Is it possible to do the transform on the same column rather than adding new column and getting value there ?

[descubriendopowerbi]

How do I do it to remove only the accent marks?

[kripamishra]

How to add spaces, for eg CamelCasetext, I want Camel Case Text..is it possible?

[sounduniverse]

Why text function is not available in power query

[clydeoporto]

Is it possible to scrape data using power query?

[femibanjo]

How about i want those special characters changed to the equivalent normal alphabet?