Forum Discussion
Solved
Pulling numeric-only values from a alphanumeric field
I am trying to do analysis and summarization of a "numeric" field in one of our systems. The issue is, when bringing it into Power BI, Power BI recognizes it as alphanumeric (rightfully so) so I am u...
Yours is the simplest solution which can be done with just the interface. However, if I were to do a custom column, it would be
// Attempts to convert the value in [column] to a number and returns null if it fails try Number.From([column]) otherwise null
Here's a solution that could be more elegant but it works: (All in Power Query). I'm sure someone who is better at M language could combine this all into a single step but this is what I'd do.
-"Duplicate" the column
-convert it to data type "Whole Number"
-"Replace Errors" with the number 0
-"Replace Values" where it is null with 0
All done!!
///Mediocre Power BI advice, but it's free///
Yours is the simplest solution which can be done with just the interface. However, if I were to do a custom column, it would be
// Attempts to convert the value in [column] to a number and returns null if it fails
try Number.From([column]) otherwise null