Pulling numeric-only values from a alphanumeric field

[EH_MW]

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 unable to do any math on it. It should be a numeric field and most people use it as such, but it is freeform in the system, so other users have entered values such as "TBD", "NA", "40M", etc. Is there any way for me to create a new column, excluding these alphanumeric values, and only pulling in the strictly numeric values, allowing me to do math on this new column?

[Ashish_Mathur]

Hi,

Hope this helps.

[kpost]

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///

[danextian · Accepted solution]

 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

[amitchandak]

@EH_MW , There are a few options discussed here, see if those can help

https://goodly.co.in/separate-numbers-from-text-excel-powerquery/

https://excelguru.ca/separate-values-and-text/

[EH_MW]

These videos deal with splitting the value. I'm looking to create a new column that completely ignores the values that have an alpha value in them. I need to do this because there are values such as "40M" instead of "40,000,000". If I simply split off the the numers and letters, I would be left with 40. Our users would prefer to ignore these values altogether until the data is cleaned up.

Instead, I'm looking for a way to create a new column that uses the monetary value if it exists (numbers only) and, if alpha characters exist in the value, set those values to 0 or ignore them completely.

[amitchandak]

@EH_MW ,

• Replace every “,” with an empty string, and remove all spaces (i.e., replace them with a zero‑length string).

• Split the result into two columns at the first transition from a digit to a non‑digit.

• Then, split the second column at the transition from a non‑digit back to a digit.

• If the first column is not blank and the second column contains suffixes like “M” or “k,” apply your specific rule for those cases.

• Define similar rules for any other suffixes or scenarios.

Provide me sample very similar to your sample I can put some code around it