Separate Text from Numbers in a mixed column

[HAL]

Hello Community:

In my data source (csv or Excel), there is a column that contains text, numbers and blanks. I need to do calculations on a subset of the numbers, so I need them in numbers format. I tried the "value" function, but as designed, it gives me an error, because it cannot convert a text to a number. So before I apply that I need to separate the text form the numbers. I tried a couple of things but so far unsuccessfully.

I am glad for a hint.

Thanks,

Hal

[Greg_Deckler]

Can you post some sample data? There are LEFT and RIGHT and FIND functions in DAX similar to Excel and as @MattAllington pointed out, there are a lot of text parsing functions in "M" as well.

[HAL]

Here is some sample data from the column:

84.000.000
84.000.000
84.000.000
***
***
184.000.000
***
74.200.000
184.000.000
74.200.000
***
***
***
***
184.000.000
ASY
ASY
ASY

In fact, I have a couple of thousand records. Currently the column ist "text" format", but I need to have access to the numeric values in the column and create average, min, max etc. from that. So I thought I create a new column with just the numeric values and format the new coklumn as "number". But I cannot figure out, how to extract the new column.

[MattAllington]

Ok. This is good. You could simply convert the data type of the column to numeric, then "remove errors", and then filter out null values. That would leave you with a column of numbers. Does that work?

[MattAllington]

When you load the data (get data - which is actually power query), you can select your column and split it into 2 based on the space. Of course that may not be what you need, but the exact transformation will depend on the exact shape of the data.