Forum Discussion

cristianml's avatar
cristianml
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Replace values/Strings from a Column in query editor

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

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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/

7 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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's avatar
      cristianml
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Hi tackytechtom ,

       

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

      could you identify why is not working ?

      • tackytechtom's avatar
        tackytechtom
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        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/