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

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]