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 compa...
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

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

  • gk2go's avatar
    gk2go
    Icon for Helper III rankHelper III
    7 years ago

    v-yulgu-msft, how to replace if it contains either of "Coca" or "Coke"?