Changes to duplicated columns also affect the original column

[GarrettU]

When I run the code below, it affects both the original column and the duplicated column. How do I make this not happen? I want the original column to stay unaffected.

let
    Source = Csv.Document(File.Contents("...\Top15MCRLs.txt"),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([RNCC] = "2" or [RNCC] = "3" or [RNCC] = "5") and ([RNVC] = "2")),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Part_No", "Part_No - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Part_No - Copy", "Strip"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"RNSC", "RNAAC", "SADC", "DAC", "HCC", "MSDSID", "RNFC", "RNJC"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Strip", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Strip", Text.Clean, type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Cleaned Text",{{"Strip", Text.Upper, type text}}),
    #"Strip" = Table.SelectRows(  #"Uppercased Text", each [Strip] = Text.Select([Strip],{"a".."z","A".."Z","0".."9"} ) )
in
    #"Strip"

(originally posted here: https://community.powerbi.com/t5/Desktop/How-to-remove-all-characters-except-alpha-numeric-in-text-c...

[danextian]

Hi @GarrettU,

In what way does your code affect the original and duplicated column? Does that happen in this step?

 #"Strip" = Table.SelectRows(  #"Uppercased Text", each [Strip] = Text.Select([Strip],{"a".."z","A".."Z","0".."9"} ) )

[GarrettU]

Yes, both columns have alphanumeric characters removed even though I am calling column "strip"

[danextian · Accepted solution]

    #"Strip" = Table.SelectRows(  #"Uppercased Text", each [Strip] = Text.Select([Strip],{"a".."z","A".."Z","0".."9"} ) )

If this is the line that's causing it, what it actually does is select all rows where the value of [Strip] is equal to the value of [Strip]  that contains just alphanumeric characters.  If you want to cleaim it up by removing non-alphanumeric characters, the function to use is Table.TransformColumns

Strip = Table.TransformColumns( #"Uppercased Text",{{"Column1", each let x = {"a".."z", "A".."Z", "0".."9"} in Text.Select(_, x ), type text}} )

[Anonymous]

This worked flawlessly, thanks! The only place I found this solution was this post. 🙂 

[GarrettU]

Thank you, works!

[GarrettU]

I was able to get it to work in a rather convoluted way by finding all existing ascii characters and removing them when adding a custom column. But I'd rather do it the way explained in the linked post. Here is the code:

Text.Combine(List.RemoveItems(Text.ToList([Part_No]),Text.ToList("{|}~ÇüéâäàåçêëèïîìÄÅÉæÆôöòûùÿÖÜø£Ø׃""áíóúñѪº¿®¬½¼¡«»░▒▓│┤ÁÂÀ©╣║╗╝¢¥┐└┴┬├─┼ãÃ╚╔╩╦╠═╬¤ðÐÊËÈıÍÎÏ┘┌█▄¦Ì▀ÓßÔÒõÕµþÞÚÛÙýݱ§÷¸°¨·¹³²■#$%&'()*+,-./:;<=>?@[\]^_ ")))

So I'd appreciate the help still!