I have a column in Power Query (standalone power query with Excel), with text like this
"Hazelnut Berries Nuts Raspberry"
I need to be able to identify if there are more than 1 instance of "nut" ("berry") in it and remove generic word, to have result as
"Hazelnut Raspberry"
I have seen this post, but it works off whole words repeated.
I'm not entirely certain about your criteria for searching for the words you want to remove (PQ is fairly limited in how it can evaluate this with built in functions anyways). This will look through that string and remove any words that start with "Nut" or "Berr".
Text.Combine(List.Transform(Text.Split("Hazelnut Berries Nuts Raspberry", " "), each if (Text.StartsWith(_, "Nut") or Text.StartsWith(_, "Berr")) then null else _), " ")
Which will get your desired output. Don't know if you need more detailed criteria for evaluating each word, but that would probably need a custom function.
List.Distinct: https://learn.microsoft.com/en-ie/powerquery-m/list-distinct should do it; something like: List.Distinct(Text.Split("Hazelnut Berries Nuts Raspberry", " "))
You might need a bit more if your list could contain multiple spaces or other "stuff"
