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.
How can I limit the column to only alphanumeric values?
Solved! Go to Solution.
Hi @Anish76 ,
If you can use Power Query, it will be easy. The idea is to add column to mark whether it contains characters other than numbers and letters.
Here's my sample data.
if [Value] = Text.Select ( [Value],{ "a".."z", "A".."Z", "0".."9" }) then 1 else 0
Then you can add this filter to write formulas in Power BI Desktop. For example,
Count rows which contains numeric, alpha, and alphanumeric values = COUNTROWS(FILTER('Table',[Contain numbers and letters]=1))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anish76 ,
What does limit mean, is it in a bunch of numbers mixed with English strings, only English letters remain?
If possible, please provide us with some sample data and expected results. And take care to protect your privacy.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your response.
I want to search only alphanumeric values in the column, which contains numeric, alpha, and alphanumeric values.
Hi @Anish76 ,
If you can use Power Query, it will be easy. The idea is to add column to mark whether it contains characters other than numbers and letters.
Here's my sample data.
if [Value] = Text.Select ( [Value],{ "a".."z", "A".."Z", "0".."9" }) then 1 else 0
Then you can add this filter to write formulas in Power BI Desktop. For example,
Count rows which contains numeric, alpha, and alphanumeric values = COUNTROWS(FILTER('Table',[Contain numbers and letters]=1))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephan,
The query only checks special characters and returns the value false. I have cells in my table that are only numeric, alphabetic, and alphanumeric with no special characters, and I want to have only alphanumeric values.
Thank you Stephen, It worked.
@Anish76 , refer if old solutions can help
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 |
---|---|
98 | |
96 | |
78 | |
72 | |
66 |
User | Count |
---|---|
136 | |
109 | |
104 | |
82 | |
73 |