Forum Discussion

sethsanu's avatar
sethsanu
Frequent Visitor
9 years ago
Solved

Extract numbers from text

What's the most efficient way to extract numbers from a column that has a combination of both numbers and text? E.g.   1 1234 "hello World" 0 "arbitrary text" should return   1 1234 null ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    In DAX you can use ISNUMBER.

    OK I should have tested that first. You were actually on the right track anyway. ISERROR(VALUE(TableName[ColumnName])) will return true if the value isn't a number, false if it is a number.

     

    In Power Query it's a two-step formula but you can nest them: Value.Is(Value.FromText([ColumnName]), Int64.Type) will return true if the row contains a number value, false if not.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

In DAX you can use ISNUMBER.

OK I should have tested that first. You were actually on the right track anyway. ISERROR(VALUE(TableName[ColumnName])) will return true if the value isn't a number, false if it is a number.

 

In Power Query it's a two-step formula but you can nest them: Value.Is(Value.FromText([ColumnName]), Int64.Type) will return true if the row contains a number value, false if not.