Clean a string using M in Power Query
Updated: May 4, 2021
In this blog I am going to show you how you can remove unwanted characters all at once from an improper text string like the following one:
"A@ntriksh Sha@rm@a _says-#Hel85lo World! 8"
The end result of the cleaned up string should be "Antriksh Sharma says Hello World"
The first thing to do is to create a variable named as DirtyString which is going to store the original string
DirtyString = "A@ntriksh Sha@rm@a _says- #Hel85lo World! 8",
Next thing to do is to create several lists that will hold the values that we want to keep.
Next all of the above Lists will be part of a single variable and in the in part we are going to concatenate all of these lists:
If you notice in the in part I have not included the Numbers and NumbersAsText list, that's only because I don't want to keep Numbers, but if you want you will have to join them as well.
The final variable is Result and it is a simple Table.SelectRows
This is how the complete code looks like:
Next what we can do is convert the whole code into a function:
Next to test the custom function I have created a dataset of 2 rows:
Let's invoke the custom function that we created above for each row:
Go to Add Column Tab and select Invoke Custom Function:
Here is the code to create the dataset if you want to try out yourself: