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.
I have a similar problem to this post that was in Excle, but need a solution in either Power Query or DAX
https://stackoverflow.com/questions/29855647/check-if-cell-contains-non-alpha-characters-in-excel
I have some heavily corrupted data for names and address fields that must ONLY contain letters. If they contain anything else i need to replace with a blank. So I guess this needs to test each charachter against a list "abcd......z" and if any don't match its FALSE
Suggestions appraciated
Mike
Solved! Go to Solution.
No worries.
I actually partially solved your problem with the code I posted above.
NumsToRemove = List.Transform({48..57 }, each Character.FromNumber(_))
48 thru 57 corresponds to the https://www.asciitable.com/
48 = 0
.
.
.
57 = 9
I left the other symbols for you to look up in the table. The link was provided as it was not my idea, I just modified it to a different need.
Proud to be a Super User!
Do you need to actually inspect the results or can a mass removal be done?
If the latter is all that is required then you can use the technique described in https://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/.
My example dataset below.
Changing of course to: Edit - 48..57 is only nums 0 - 9; use the https://www.asciitable.com/ to include other symbols
NumsToRemove = List.Transform({48..57 }, each Character.FromNumber(_))
Returns the following when using:
Text.Remove([Part Number],NumsToRemove)
It leaves the space so then just use:
Text.TrimStart([Result])
Proud to be a Super User!
Hi Chris
Just need to wipe it if it isnt all letters as its garbagge data.
I'll check the link thanks
The link is to keep only numbers os don't see how that helps as I need the opposite keep only text?
I could sort of use your power bi method except i woudl have to list every single charachter that isnt a letter. I have some charachters that are really weird so its actually impossible. i think I need ot do it the other way confrim that no charachter is not in the list abc...z? Is there a way to do that?
Mike
Please read my statement above the first code block.
Proud to be a Super User!
Sorry thought this was code for only keeping numbers.
Where is says "So what does this do? It actually creates a list of non-contiguous numbers (33-45, 47, 58-126), then transforms each value in the list into it’s alphanumeric equivalent. A partial set of the results is shown here:" Are these numbers to alphanumeric equivalents a standard thing that is listed somewhere? I'm struggling to understand how List.transform({33..45 is related to the figure below as 33 seems to be "L". Or is 33 the first number in list and 33="!"?
I can see if i could remove A..Z and a..z and be left with nothing that woudl be the right test
No worries.
I actually partially solved your problem with the code I posted above.
NumsToRemove = List.Transform({48..57 }, each Character.FromNumber(_))
48 thru 57 corresponds to the https://www.asciitable.com/
48 = 0
.
.
.
57 = 9
I left the other symbols for you to look up in the table. The link was provided as it was not my idea, I just modified it to a different need.
Proud to be a Super User!
Got it great thanks
Just for completeness here is my solution
let CharsToRemove = List.Transform({33..44,46..47,58..64,123..255}, each Character.FromNumber(_)), NumToRemove = List.Transform({48..57}, each Character.FromNumber(_)), Source = Csv.Document(File.Contents("U:\Dropbox\In-n-Out\Concorde\
Then this where I compare the length without special charachters with the length without either special or numbers. If they are the same I keep the version witohut special (which by defualt has no numbers) or it is blank. So this cleans up the random special charachters or fields just containing special charachters, and excludes any data containing numbers.
#"Added Custom2" = Table.AddColumn(#"Added Custom", "PEADDR3", each if Text.Length(Text.Remove([PEADDR3 RAW],CharsToRemove))= Text.Length(Text.Remove(Text.Remove([PEADDR3 RAW],CharsToRemove),NumToRemove)) then Text.Remove([PEADDR3 RAW],CharsToRemove) else null),
PEADDR£ RAW is my starting data and PEADDR3 is the clean data
Mike
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 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |