Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Employee
Employee

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
Anonymous
Not applicable

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

 

Anonymous
Not applicable

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
Employee
Employee

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors