Replace multiple values containing specific letters in Power Query

Hi vineshparekh,

• edit 2nd step YourSource = Source (refer to your date after equals sign)
• in 3rd step CharsToBeReplaced you can add more characters which should be deleted (just put them in quotes one by one and separate by comma)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslKIiIxSitWJVgLSClaxcKauroJSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Data" = _t]),
    YourSource = Source,
    CharsToBeReplaced = {":", "]", "-"},
    StepBack = YourSource,
    Ad_CleanedData = Table.AddColumn(StepBack, "Cleaned Data", each
        Text.Trim(
            List.Accumulate(
                List.Buffer(CharsToBeReplaced),
                [Original Data],
                (s,c)=> Text.Replace(Text.From(s), c, "")
            )), type text)
in
    Ad_CleanedData

Hi,

In one step  with List.ReplaceMatchingItems

Text.Combine(
    List.ReplaceMatchingItems(
       Text.ToList([Original Data]),
       List.Transform(Text.ToList(":;,!]){[|- "), each {_,""}))
   )
)

 Stéphane

If the characters are in the middle of your text, did you want to keep it or remove them too.  If you want to remove them regardless of where they are, the other solutions will work.

If you need to keep the characters in the middle the below will work.

Add custom column

Text.Trim([Original Data], Text.ToList(" :;[]-")) 

dufoq3 Thanks did not realize you could put a list in Text.Trim.

Hi vineshparekh, done.

Specify chars and words to remove (I've already added all currencies):

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wso1R0jXQMzBUcHZ0iVFSitWJVjID8g0UQoNdwDyQAiMjYz1LsBBUSbSCqameqQGYHREZpWAVC9NpagAySQGkLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    CharsToRemove = " ,[]:/|\*-+=_()';.'""{}!?`~@#$%^&*",
    WordsToRemove = List.Buffer({"AED", "AFN", "ALL", "AMD", "AOA", "ARS", "AUD", "AUD", "AUD", "AUD", "AZN", "BAM", "BBD", "BDT", "BGN", "BHD", "BIF", "BND", "BOB", "BRL", "BSD", "BTN", "BWP", "BYN", "BZD", "CAD", "CDF", "CHF", "CHF", "CLP", "CNY", "COP", "CRC", "CUP", "CVE", "CZK", "DJF", "DKK", "DOP", "DZD", "EGP", "ERN", "ETB", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "FJD", "GBP", "GEL", "GHS", "GMD", "GNF", "GTQ", "GYD", "HNL", "HTG", "HUF", "IDR", "ILS", "ILS", "INR", "IQD", "IRR", "ISK", "JMD", "JOD", "JPY", "KES", "KGS", "KHR", "KMF", "KPW", "KRW", "KWD", "KZT", "LAK", "LBP", "LKR", "LRD", "LSL", "LYD", "MAD", "MDL", "MGA", "MKD", "MMK", "MNT", "MRO", "MUR", "MVR", "MWK", "MXN", "MYR", "MZN", "NAD", "NGN", "NIO", "NOK", "NPR", "NZD", "OMR", "PAB", "PEN", "PGK", "PHP", "PKR", "PLN", "PYG", "QAR", "RON", "RSD", "RUB", "RWF", "SAR", "SBD", "SCR", "SDG", "SEK", "SGD", "SLL", "SOS", "SRD", "SSP", "STD", "SYP", "SZL", "THB", "TJS", "TMT", "TND", "TOP", "TRY", "TTD", "TWD", "TZS", "UAH", "UGX", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "USD", "UYU", "UZS", "VEF", "VND", "VUV", "WST", "XAF", "XAF", "XAF", "XAF", "XAF", "XAF", "XCD", "XCD", "XCD", "XCD", "XCD", "XCD", "XOF", "XOF", "XOF", "XOF", "XOF", "XOF", "XOF", "XOF", "YER", "ZAR", "ZMW"}),
    StepBack = Source,
    Ad_Cleaned = Table.AddColumn(StepBack, "Cleaned", each 
     [ a = Text.Trim([Column1], Text.ToList(CharsToRemove)), //remove Chars
       b = List.Select(Text.Split(a, " "), each _ <> ""), //split to list and remove spaces
       c = List.RemoveMatchingItems(b, WordsToRemove),
       d = Text.Combine(c, "")
     ][d], type text)
in
    Ad_Cleaned