Contains functionality in Power Query for Power BI

[Krc721]

Hi.

I am new to DAX and was hoping there was a way to do this:
I have a column with poor data quality, meaning there are a lot of variations that may be present with the word High in them.  I do not have a comprehensive list of what these could be.  Few examples, High Impact, Moderate-High, Mod-High, HIGH, High, etc.   Is there any way to say if this string contains High (anywhere case insensitive then replace the value in that field with High?

I know Find & Replace and Substitute but those you have to know what derivations there could be. 

Thanks in Advance.

[Krc721]

@Ahmedx @PijushRoy   Hi Guys, worked around and got the solution.

Table.ReplaceValue(#"Change Value", each [Rating], each if Text.Contains([Rating],"High",Comparer.OrdinalIgnoreCase) then "High" else [Rating], Replacer.ReplaceValue, {"Rating"})    

In the Applied Steps, I did an insert step after and put the formula there. It used the same column, Rating, as my original values and left null/blank as null/blank and other values without the word High in them alone.  All derivations of High in the data set were handled (still need to test the Mod-High) but so far that worked.  Just wanted to share since both of your help gave me a big boost in noodling out a solution.

[Ahmedx · Accepted solution]

although when you load data into power bi it will be as you want because power bi is not case sensitive
but all of these can be done in power query the way you want

pls try this

[Krc721]

Hi Ahmed, I closed the solution prematurely.  I tested it as the column has other values and it is converting all of them to High when it shouldn't.  Any thoughts?  There are many values in the column and there is no set defined list of values hence the request for something to look and only convert the ones with High, leave null/blank and ones without High in them as is.

[Krc721]

I think that might work; however, as written it makes all nulls = High.  So I changed x <> to x= that doesn't happen.  Need to test some other values in data that is not present now (like low, medium) to ensure all works properly.  Thank you so much!

[Krc721]

Thanks for pasting the image as I am locked down from downloads.  I will give it a go. 

[PijushRoy]

Hi @Krc721 

Please use CONTAINSSTRING dax function in calculated column
Find example - https://learn.microsoft.com/en-us/dax/containsstring-function-dax

https://dax.guide/containsstring/

https://www.sqlbi.com/tv/containsstring-containsstringexact-dax-guide/

Let me know if that works for you

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Krc721]

Thank you for your response.  That creates a new column with numeric value but does not replace the words in the existing column to standardize on one word, High, that will be used in logic.  I don't know all the variations that could exist in the data so wouldn't this potentially leave some records out?