cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RE
Helper I
Helper I

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

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

Hi @RE,

 

Here are two options for your reference

 

1. add a conditional column

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Leroy
Helper I
Helper I

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"

 

Leroy_0-1657171812220.jpeg

 

Any assistance would be appreciated

 

I can do this in Dax

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

but Mcode isn't working 

v-yulgu-msft
Microsoft
Microsoft

Hi @RE,

 

Here are two options for your reference

 

1. add a conditional column

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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 attachedCapture.PNG

 

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

 

Thx,

Ciprian

monmon_bi
Frequent Visitor

You can use Text.Contains.

 

For example,

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

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]

monmon_bi
Frequent Visitor

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] 

Mariam1991
Helper II
Helper II

Hi @RE

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

 

 

 

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.