Forum Discussion
Separate Text from Numbers in a mixed column
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
4 Replies
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.
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.
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.
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?
