Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Solved! Go to 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}} )
Proud to be a Super User!
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"} ) )
Proud to be a Super User!
Yes, both columns have alphanumeric characters removed even though I am calling column "strip"
#"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}} )
Proud to be a Super User!
This worked flawlessly, thanks! The only place I found this solution was this post. 🙂
Thank you, works!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |