Replace values/Strings from a Column in query editor

[cristianml]

Hi,

I need help with the following in query editor. It gives an error:

I'm trying to replace IF those values/strings are in each row from from the COLUMN1

Could you please help me to fix the error ? 

= Table.ReplaceValue(#"Replaced Value1",
each if "String1" in [COLUMN1] then "01",
else if "String2" in [COLUMN1] then "02",
else if "String3" in [COLUMN1] then "03",
else if "String4" in [COLUMN1] then "04",
else if "String5" in [COLUMN1] then "05",
else if "String6" in [COLUMN1] then "06",
else if "String7" in [COLUMN1] then "07",
else "NADA"),Replacer.ReplaceText,{"COLUMN1"})

The string is NOT equal the cell but inside a bunch of words so need to replace only that string:

Meaning:  XXXXXXString1XXX     > replace with >   XXXXXX01XXX

Thanks

[tackytechtom]

Hi @cristianml ,

Spontaneously, I could think of that you might have forgotten to change the the source of the next step. But that is just a wild guess. Do you have the possibility to upload the file or alternatively, upload the data and the Power Query M code?

thanks!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @cristianml ,

Does it work with the following code?

= Table.ReplaceValue(#"Replaced Value1", "Custom",
each if Text.Contains([COLUMN1], "String1") then "01"
else if Text.Contains([COLUMN1], "String2") then "02"
else if Text.Contains([COLUMN1], "String3") then "03"
else if Text.Contains([COLUMN1], "String4") then "04"
else if Text.Contains([COLUMN1], "String5") then "05"
else if Text.Contains([COLUMN1], "String6") then "06"
else if Text.Contains([COLUMN1], "String7") then "07"
else "NADA", Replacer.ReplaceText,{"COLUMN1"})

Let me know if this fixes the issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[cristianml]

Hi @tackytechtom ,

Seems ok with no error but is not replacing anything : See below :

could you identify why is not working ?

[tackytechtom]

Hi @cristianml 

My bad, I forgot to specify the column where all this should happen.... Can you try this? 🙂

= Table.ReplaceValue(#"Changed Type", each [COLUMN1], each if Text.Contains([COLUMN1], "January") then "01"
else if Text.Contains([COLUMN1], "February") then "02"
else if Text.Contains([COLUMN1], "March") then "03"
else if Text.Contains([COLUMN1], "April") then "04"
else if Text.Contains([COLUMN1], "May") then "05"
else if Text.Contains([COLUMN1], "June") then "06"
else if Text.Contains([COLUMN1], "July") then "07"
else "NADA", Replacer.ReplaceText,{"COLUMN1"})

Let me know! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[cristianml]

Hi @tackytechtom ,

Almost there. I don't understand why it changes all instead of only the string:

I need to keep the rest of the string/text:

Is there somenting that we can change in the last formula to keep that ?

[tackytechtom · Accepted solution]

Hi @cristianml ,

Now I see where you wanna get to! 🙂 This one right?

Try this:

Table.ReplaceValue(#"Changed Type", each [COLUMN1], 
each if Text.Contains([COLUMN1], "January") then Text.Replace([Custom], "January", "01")
else if Text.Contains([COLUMN1], "February") then Text.Replace([Custom], "February", "02")
else if Text.Contains([COLUMN1], "March") then Text.Replace([Custom], "March", "03")
else if Text.Contains([COLUMN1], "April") then Text.Replace([Custom], "April", "04")
else if Text.Contains([COLUMN1], "May") then Text.Replace([Custom], "May", "05")
else if Text.Contains([COLUMN1], "June") then Text.Replace([Custom], "June", "06")
else if Text.Contains([COLUMN1], "July") then Text.Replace([Custom], "July", "07")
else "NADA", Replacer.ReplaceText,{"COLUMN1"})

Is this the solution? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[cristianml]

hI @tackytechtom ,

Thanks !Finally I solved it. I Changed  Text.Replace([Custom]  with Text.Replace([COLUMN1]

Thank you !:)