Clean Data

[Centaur]

Hello,

How could I remove all:

1. Dashes
2. Slashes
3. Periods
4. Parenthesis (both opening "(" and closing ")"
5. # sign
6. Comma
7. Leading zeros
8. Trailing zeros
9. Spaces

thank you very much

[Anonymous · Accepted solution]

For a column:

= Table.TransformColumns(TableOrPriorStepName, {{"ColumnName", each Text.Remove(_, {"-", "/", ".", "(", ")"})}})

I didn't add all of your characters, but you get it.

--Nate

[Anonymous · Accepted solution]

Nasically, you are just providing a list of quoted characters to remove, like

{"-", "(", ")", " ", "/", ",", ".", "#"} 

I'd need to see the leading zeros and trailing zeros, but if you want to get rid of zeros, just add "0" to the list.

You could also use

each Text.Select([ColumnName], {{"A".."Z"}, {"a".."z"}, {"1".."9"}}). This will keep only letters and text numbers except zero.

--Nate

[Anonymous · Accepted solution]

Nasically, you are just providing a list of quoted characters to remove, like

{"-", "(", ")", " ", "/", ",", ".", "#"} 

I'd need to see the leading zeros and trailing zeros, but if you want to get rid of zeros, just add "0" to the list.

You could also use

each Text.Select([ColumnName], {{"A".."Z"}, {"a".."z"}, {"1".."9"}}). This will keep only letters and text numbers except zero.

--Nate

[Centaur]

thank you very much!

[Anonymous · Accepted solution]

For a column:

= Table.TransformColumns(TableOrPriorStepName, {{"ColumnName", each Text.Remove(_, {"-", "/", ".", "(", ")"})}})

I didn't add all of your characters, but you get it.

--Nate

[Centaur]

Hi Nate, I thought I could figure that out but I am sorry.  I cant.  Could you kindly add the other ones?  I am a little confused on the leading and trailing 0's, spaces too (at the end and beginning).

grateful for the help. 

[AlexisOlson · Accepted solution]

Text.Trim will clean up leading and trailing spaces but you can also use it to trim other characters. So you could write this

each Text.Trim(Text.Trim(Text.Remove(_, {"-", "/", "\", ".", "(", ")", "#", ","})), "0")

to remove the listed characters, then remove leading and trailing spaces, then remove leading and trailing "0".

[Centaur]

Nice!  I resorted to doing it individually but this will certainly clean it up.