Replace multiple words simultaneously in M Query

[Anonymous]

Hi there

I have a 'Comments' column and I want to remove about 70 words to clean it up. I am loath to use replace, because it creates 70 steps and takes ages. 

I have found Table.TransformColumnTypes combined with Text.Remove (see below syntax) but this only replaces single characters, not words. 

= Table.TransformColumns(PreviousStep,{{"Text Comments", each Text.Remove(_,{})}})

Is there a function I can apply to my table, or some other fancy way, within M Query (I know about word clouds) that I can do this?

Jemma 🙂 

[negi007]

@Anonymous will it be possible for you to share the input or output you are expecting from your data. Please share data in text format so that we can do the testing at our end and suggest a solution to you. 

[VijayP]

@Anonymous can you share one or two examples

[Anonymous · Accepted solution]

I found a soltution

https://www.howtoexcel.org/power-query/bulk-replace-values/

[VijayP]

@Anonymous 

Try using add column from example and try to give few examples to train power query to remove respective words ! may be you need to give few examples 4 to 5. Let me know if you are able to do that!

[Anonymous]

Sorry @VijayP no chance. The text is too detailed, example column gets instantly confused and tries to transform the whole sentence.