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 m...
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/

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/

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

      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's avatar
        tackytechtom
        Icon for Most Valuable Professional rankMost Valuable Professional
        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/