Replace values containing specific letters in cells

[Vineshdparekh]

Hi,
I am stuck in Power Query where I have many words containing specific letters which I want to remove and keep rest of the letters.
For example,
Column
Current Data Required Data
XYZ : XYZ
XYZ :] XYZ
[: XYZ XYZ

Is there a way to remove all these additional characters in one step in Power Query?
Thank you.

 

[p45cal]

Try something along the lines of:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedCustom = Table.AddColumn(Source, "Required Data", each Text.Combine(List.RemoveItems(Text.ToList([Current Data]),{"[",":"," ","]"})))
in
    AddedCustom

 

[Vineshdparekh]

Try something along the lines of:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedCustom = Table.AddColumn(Source, "Required Data", each Text.Combine(List.RemoveItems(Text.ToList([Current Data]),{"[",":"," ","]"})))
in
    AddedCustom

Thank you so much! This works great.
I forgot to mention in the original post that I want to remove these additional characters but also I want to remove some words.
For example,

XYZ SN:
Result XYZ

XYZ correction from previous month
Result XYZ.

Thanks again!

 

[p45cal]

It's possible to remove a list of words from a column but it's a separate operation from removing single characters. It's also important the order in which these are carried out (we shouldn't be removing spaces if we're using those spaces as delimiters to words). The likes of:

Table.AddColumn(#"Changed Type", "NewColumnName", each Text.Combine(List.ReplaceMatchingItems(Text.Split([ColumnToWorkOn]," "),List.Transform({"correction","from"}, each {_, null}))," "))

should do it. The list of words to remove here is hard-coded {"correction","from"} but this could be obtained from another table.

Why don't you attach a workbook with all starting possibilities and show some results you expect?

 

[p45cal]

Try something along the lines of:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedCustom = Table.AddColumn(Source, "Required Data", each Text.Combine(List.RemoveItems(Text.ToList([Current Data]),{"[",":"," ","]"})))
in
    AddedCustom

I've since come across a simpler way:

= Table.AddColumn(PreviousStep, "Required Data", each Text.Remove([Current Data],{"[",":"," ","]"}))

 

[Chihiro]

Just another way to remove special characters (especially when exact characters are unknown).

= Table.AddColumn(PreviousStep, "Required Data", each Text.Select([Current Data],{"A".."z", " "}))

Usually requires another step to clean up extra spaces.