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.
Using M in Query Editor, I'm looking for a simple and efficient means by which to remove non-letters from a text column in a table. For example, if the first two rows within a column have values "this is not all_text!" and "1234Letters$" I'd like them to become "this is not all text" and "Letters". A few entries also have emojis that are pulled into the query, and I'd also like to scrub those out. Any suggestions are appreciated!
Solved! Go to Solution.
No need to lowercase. Try this:
Text.Remove(..YourText.., List.Transform({0..64, 91..96, 123..50000}, each Character.FromNumber(_))))
This will remove every character within the range of 0..50000 that isn't a..z or A..Z. So if you have some special signs that you want to include, you need to find the number (Character.ToNumber) and include those numbers in the list above.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I'm not aware of a "non-letter"-class in M.
Instead we have to define specificly a black- or whitelist: What shall stay in or what shall be removed?
Which route would you prefer?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I'm thinking I should set everything to lowercase, then create a whitelist for a-z. Probably the best way to eliminate odd characters. Thank you.
Greg B
No need to lowercase. Try this:
Text.Remove(..YourText.., List.Transform({0..64, 91..96, 123..50000}, each Character.FromNumber(_))))
This will remove every character within the range of 0..50000 that isn't a..z or A..Z. So if you have some special signs that you want to include, you need to find the number (Character.ToNumber) and include those numbers in the list above.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke
This also removes characters written in non-latin alphabetics....
Any suggestions?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Nice!
Well, Text.Remove provides a nice way of removing a list of characters but Text.Replace does not provide the functionality.
https://msdn.microsoft.com/en-us/library/mt260472.aspx
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |