Replace multiple values containing specific letters in Power Query

[vineshparekh]

Hello,

I am stuck in Power Query where I want to replace some characters within the column and keep rest of the characters. 

Is there a way to remove these extra characters with one step? 

For example,

Original Data           Required Data

: XYZ                        XYZ

XYZ :]                       XYZ

XYZ --                      XYZ

Thank you.

[dufoq3 · Accepted solution]

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

[slorin · Accepted solution]

Hi,

In one step  with List.ReplaceMatchingItems

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

 Stéphane

[spinfuzer · Accepted solution]

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.

[vineshparekh]

Hi All,

Thank you for these responses and this works perfectly for removing the characters.

I have a question when I work on the report, I have few words along with the characters that I want to remove. Could you please help? Please note that there are spaces too.

Original Data           Required Data

: XYZ  "                      XYZ

XYZ :]                        XYZ

XYZ --    "                  XYZ

XYZ CAD                   XYZ

=" XYZ                      XYZ

Thanks again,

Vinesh.

[dufoq3]

Hi, could you share sample of real data please? We can create such query based on this new sample, but it won't probably work with real data.

[vineshparekh]

Hi, please see the attached and unfortunately now the query is not working for the characters as well. I want to get a column removing all characters, spaces, specific words like CAD or USD to get the numbers or XYZ.

Total

="-0.01 CAD"

60.00 USD

="-223.90 USD"

[ 55.50

XYZ :]

60.50 CAD "

Required Data

0.01

60

223.9

55.5

XYZ

60.5

Query I used:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Total", type text}}),
RemoveChar = Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([Total]),
List.Transform(Text.ToList(":;,!]){[|- "), each {_,""}))
)

in
RemoveChar

[dufoq3 · Accepted solution]

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

[vineshparekh]

Wow! This is some skills.

Works perfectly. Thanks much! 

[dufoq3]

You're welcome 😉

[spinfuzer · Accepted solution]

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.

[dufoq3]

This could be done more easily I would say:

Add this as custom column:

= Text.Trim( [Original Data], { " ", ":", "]", "-" } )

[slorin · Accepted solution]

Hi,

In one step  with List.ReplaceMatchingItems

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

 Stéphane

[dufoq3 · Accepted solution]

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