Cleaning a column of non-key words

[Anonymous]

Hi guys

Not sure this is one for Power BI really, I can use SQL also if this is better, but anyway here is my challenge:

I have a column containing medical conditions. I have a fabulous word cloud which shows the most commonly occuring medical conditions people have claimed for. 

The issue with the medical condition column, is it has all sorts of words which are not key, for example AND IN ON OR LEFT RIGHT UNCLASSIFIED THE etc... the list goes on.

I have a terribly long conditional column called 'Condition Summary' which has half cleaned the condition column, and i've done some replacing to remove conjunctions but it's still not great and my query is very long as a result.

Does anyone have an idea on how I can clean this a little more efficiently? 

Jemma

[v-kelly-msft]

Hi @Anonymous ,

Please try if this works: http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ & report back.

Just leave the column "ReplaceWith" blank.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Stachu]

The conditional column is in DAX, correct? If so then it should be more performant (and more likely easier to do) in the query editor (M), you could use something like 
https://docs.microsoft.com/en-us/powerquery-m/text-replace

checking for the list of the irrelevant words 

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5