Forum Discussion

HAL's avatar
HAL
Advocate I
10 years ago

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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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's avatar
    HAL
    Advocate I
    10 years ago

    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's avatar
      MattAllington
      Community Champion
      10 years ago

      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?