Forum Discussion

RE's avatar
RE
Icon for Helper I rankHelper I
9 years ago
Solved

M if text contains

I'm fairly new to M and I'm not sure if this functionality is even avaliable but what I'm trying to do is replace text that contians a specific word. For example, In my data set I have the same company listed with different names:

 

Coke 
Coca Cola

Coca-Cola

Coke Co. 

 

What I want is write something that will tell it to change any text that contains "Coca" to "Coca Cola". 

Need Help
Show and Tell
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi RE,

     

    Here are two options for your reference

     

    1. add a conditional column

     

    Corresponding M query:

    = Table.AddColumn(#"PreviousStep", "Custom", each if Text.Contains([Column1], "Coca") then "Coca Cola" else [Column1] )

    2. Replace values on original column directly.

    #"Replaced Value" = Table.ReplaceValue( #"PreviousStep" ,each [Column1],each if Text.Contains([Column1], "Coca") then "Coca Cola" else [Column1],Replacer.ReplaceValue,{"Column1"})

    Best regards,
    Yuliana Gu

9 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi RE,

     

    Here are two options for your reference

     

    1. add a conditional column

     

    Corresponding M query:

    = Table.AddColumn(#"PreviousStep", "Custom", each if Text.Contains([Column1], "Coca") then "Coca Cola" else [Column1] )

    2. Replace values on original column directly.

    #"Replaced Value" = Table.ReplaceValue( #"PreviousStep" ,each [Column1],each if Text.Contains([Column1], "Coca") then "Coca Cola" else [Column1],Replacer.ReplaceValue,{"Column1"})

    Best regards,
    Yuliana Gu

    • cbusila's avatar
      cbusila
      Frequent Visitor
      7 years ago

      What if you have for example a table with 3 columns and the content of the columns can be grouped under certain rules each rules with 3 criterias. I want to create a custom column and put on rows the coresponding rule coding.

       

      In order to put the rule I have to check if column 1 from the table contains the criteria 1 of Rule 1, if column 2 from the table contains the criteria 2 of Rule 1 and if column 3 from the table contains the criteria 3 of Rule 1; if all are met then I put the code for rule 1

      Please see the print screen attached

       

      Do you know if there is a way to create a M code for this kind of situation?

       

      Thx,

      Ciprian

  • Mariam1991's avatar
    Mariam1991
    Icon for Helper II rankHelper II
    9 years ago

    Hi RE

    In power query, you select the column which contains these values ,  with a right click , you choose "replace values"

     

     

     

     

  • monmon_bi's avatar
    monmon_bi
    Frequent Visitor
    9 years ago

    If you were to use M language function, there's one called Text.Contains. This will return true or false based on the outcome.

     

    For example,

    [Custom] = if Text.Contains("Coca", [Company Name]) then "Coca Cola" else [Company Name] 

  • monmon_bi's avatar
    monmon_bi
    Frequent Visitor
    9 years ago

    You can use Text.Contains.

     

    For example,

    [Custom] = if Text.Contains("Coca", [Company]) then "Coca Cola" else [Company]

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      If anyone ever finds this topic with a similar question, this solution doesn't work.

      Not sure if the formula requirements changed over the years, but it should be like this

       

      if Text.Contains([Company], "Coca") then "Coca Cola" else [Company]

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

    I've added this code but

     Table.AddColumn(#"Filtered Rows4", "Custom", each if Text.Contains([Cost Recovery Original column], "Slaughter") then "Slaughter" else "Not Slaughter")

    I'm getting the incorrect result. I'm expecting row 35 which contains the word "Slaughter" to return "Slaughter" not "Error"

     

     

    Any assistance would be appreciated

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I can do this in Dax

      Custom test = CALCULATE(CONTAINSSTRING(MAX('All Data'[Cost Recovery Original column]),"Slaughter"))

      but Mcode isn't working