Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Contains functionality in Power Query for Power BI

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.

Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    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

7 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    Hi Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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?  

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.